using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Data.SqlTypes;
using System.Data.OleDb;
using System.Text;

public partial class app_frame_import : System.Web.UI.Page
{
    string strPagesetRowID = "a46b3a52ce2a5ac6"; // simple_import
    DataSet dseInputInfo = new DataSet();
    DataSet dsePageInfo = new DataSet();
    HtmlForm frmObj;
    string strErrMessage = "";
    string refererURL = "";
    string dtFormat = "MM-dd-yyyy HH:mm:ss";
    private int recordsAffected = 0;
    private StringBuilder errors = new StringBuilder();
    private int recordCount = 0;
    string strImportAf_row_id = "";
    private Hashtable htRecordsInserted = new Hashtable(); // Added by 'Imran Gani' on 28/03/12, to get No. of records have inserted in DB.
    private StringBuilder sbMasterInserts = new StringBuilder(); // Added by 'Imran Gani' on 29/03/12, to get Master records info which are inserted in DB.
    //protected void Page_Error(object sender, EventArgs e)
    //{
    //    Response.Redirect("documents.aspx");
    //}
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString != null)
        {
            if (Request.QueryString["file"] != null)
            {
                giveFile();
                return;
            }
        }
        security hdds = new security();
        //Code added by Sampath on 16-Nov-2011 for IPP Vertical privilege
        if (Request.QueryString["mn"] != null)
        {
            if (!hdds.checkObjectMenuAccess(Request.QueryString["mn"]))
            {
                tblcontrols.Visible = false;
                lblMess.Text = wrapItAsMessage("<img src=\"Images/info.gif\" alt=\"\"></img>&nbsp;You do not have sufficient access permission.", true);
                return;
            }
        }

        FileUpload1.Attributes.Add("onchange", "javascript:checkExtension();");

        //if(Request.QueryString!=null)
        //    if (Request.QueryString["mode"] != null)
        //    {

        //        Response.Write(PerformDelete());
        //        return;
        //    }
        loadValues();
        lblXLSLink.Text = "";
        lblStatusUpload.Text = "";
        lblHeader.Text = "Import from Excel/CSV";

        //if (!IsPostBack) setFonts();
        //if (!IsPostBack) this.Error += new System.EventHandler(this.Page_Error);
        if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL);
        if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL);

        p_simple_import_target_object.Attributes.Add("onchange", "javascript:ResetMessage(); ClearDownloadTemplate(); getRaletedFields();"); // Modified by 'Imran Gani' on 30-Jul-2013, to include 'getRalatedFields' function.

        hdds.logMenuClick(Request.QueryString["mn"].ToString());

        setRequestURL();
        if (this.Master.FindControl("Form1") != null)
        {
            frmObj = (HtmlForm)this.Master.FindControl("Form1");
        }
        PageInfoLoad PIL = new PageInfoLoad();
        dsePageInfo = PIL.GetPageSetPageCtrlInfo(strPagesetRowID);

        CheckEditOperation();
        btnSave.Visible = false;
        btnReset.Visible = false;
        if (!IsPostBack)
        {
            p_simple_import_target_object_SelectedIndexChanged(null, null);
            loadObjectFieldsToAvoidDuplicates(p_simple_import_target_object.SelectedValue); // Added by 'Imran Gani' on 30-Jul-2013, to load object related fields.
        }
        if (IsPostBack)
        {
            getpageicons();
            if (hfdeleteclick.Value == "1")
                LoadStats(p_simple_import_target_object.SelectedValue);
            return;
        } //Modified by srivatsan

        if (btnUpdate.Visible)
        {
            lblHeader.Text = "Import - Edit";
            loadMasterValues(false);
        }
        if (Request.QueryString["CPY"] != null)
        {
            if (Request.QueryString["CPY"] == "0")
            {
                lblHeader.Text = "Import - Copy";
                loadMasterValues(true);
                //loadChildValues();
                //showParamterContent(true, Request.QueryString["PK"].ToString());
                //p_format_masks_name.Text = getSeqNumber();
            }
        }

        getpageicons(); // Added by srivatsan
        AddPageToolTip(dsePageInfo);
        isViewMode();
        //if (!Page.IsPostBack)
        //    CollapsiblePanelExtender1.Collapsed = true;
        checkAdvanceOptions();
    }
    private void isViewMode()
    {
        if (HttpContext.Current.Request.QueryString["VMODE"] != null)
            if (HttpContext.Current.Request.QueryString["VMODE"] == "1")
            {
                DynamicDisplayI ddi = new DynamicDisplayI();
                ddi.disableControls(this);
                ddi = null;
            }
    }

    //private void setFonts()
    //{
    //    lblDetailsCaption.Font.Name = "Arial";
    //    lblDetailsCaption.Font.Size = FontUnit.Smaller;
    //    lblDetailsText.Font.Name = "Arial";
    //    lblDetailsText.Font.Size = FontUnit.Smaller;
    //    Label1.Font.Name = "Arial";
    //    Label1.Font.Size = FontUnit.Smaller;  
    //}
    private void giveFile()
    {
        SqlCommand cmd = new SqlCommand();
        HDDatabase hdd = new HDDatabase();
        try
        {
            string sql = "SELECT * FROM sy_import_templates where af_row_id='" + Request.QueryString["file"].ToString() + "'";
            cmd = new SqlCommand(sql);
            cmd.Connection = hdd.GetConnection();
            cmd.Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    //Response.BinaryWrite((byte[])reader["fileContent"]);
                    //string ss = reader["file_name"].ToString();
                    Response.Clear();
                    Response.Buffer = true;
                    //Response.ContentType = "multipart/mixed";
                    Response.ContentType = "application/x-download";
                    Response.AddHeader("content-disposition", "attachment;filename=\"" + Request.QueryString["name"].ToString() + ".xls" + "\"");
                    //Response.Cache.SetCacheability(HttpCacheability.NoCache);
                    //Response.BinaryWrite((byte[])reader["content"]);     
                    byte[] content = (byte[])reader["template_content"];
                    Response.OutputStream.Write(content, 0, content.Length);
                }
            }
            #region alternate way
            //DataTable dt =  hdd.GetDataTable(sql);
            //byte[] cont = (byte[]) dt.Rows[0]["fileContent"];
            //Response.Clear();
            //Response.Buffer = true;
            //Response.ContentType = "multipart/mixed";
            //Response.AddHeader("content-disposition", "attachment;filename=" + dt.Rows[0]["file_name"].ToString());
            ////Response.Cache.SetCacheability(HttpCacheability.NoCache);
            ////Response.BinaryWrite((byte[])reader["content"]);
            //Response.OutputStream.Write(cont, 0, (int)dt.Rows[0]["file_size"]);
            //dt = null;
            #endregion
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in giveFile function. Error: " + e.Message);
        }
        finally
        {
            hdd = null;
            if (cmd.Connection != null)
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
        }
    }
    private void setRequestURL()//implemented by sanal on 13 Dec 2007
    {
        if (!IsPostBack)
            hfRequestURL.Value = Request.UrlReferrer.ToString();
    }
    private void loadMasterValues(bool copyMode)
    {
        try
        {
            string sPageKey = "";
            if (Request.QueryString["PK"] != null)
                sPageKey = Request.QueryString["PK"].ToString();
            if (sPageKey == "") return;
            HDDatabase hd = new HDDatabase();
            DataSet ds = new DataSet();
            // Added by srivatsan on June 15 2009 for owner id field
            ds = hd.GetDataset("select name,description,file_type,file_size,file_name,download_url,af_row_id,created_by,created_at,owner_id,modified_by,modified_at from sy_documents where af_row_id='" + sPageKey + "'");//no content should be retrieved

            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {

                }
            }

            ds = null;
            hd = null;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in messages.aspx:loadMasterValues: " + e.Message);
        }
    }
    private void loadValues()
    {
        if (IsPostBack) return;
        p_simple_import_target_object.Items.Clear();
        DataSet ds = new DataSet(); DataSet dsObj = new DataSet(); DataSet dsPage = new DataSet();
        HDDatabase hd = new HDDatabase();
        try
        {
            //ds = hd.GetDataset("select mem_id,mem_name from sy_acr_mem where acr_id='b3df0aef2ac573e2' order by mem_seq");
            //if (ds.Tables.Count > 0)
            //{
            //    if (ds.Tables[0].Rows.Count > 0)
            //    {
            //        foreach (DataRow dr in ds.Tables[0].Rows)
            //        {
            //            //p_import_temp_source_type.Items.Add(new ListItem(dr["mem_name"].ToString(), dr["mem_id"].ToString()));
            //        }

            //    }
            //}
            //ds = hd.GetDataset("select object_id from sy_obj_acc where role_id='" + Session["roleid"].ToString() + "' and [create]=1");
            ds = hd.GetDataset("select distinct [object_id],sequence,modified_at,link  from sy_import_templates where link like ('%.xls') order by sequence,modified_at,link");
            string temp = "";
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        temp += "'" + dr["object_id"].ToString().Replace("'", "''") + "',";
                    }
                }
            }
            if (temp.Length > 0)
                temp = "(" + temp.TrimEnd(',') + ")";
            if (temp.Length > 0)
            {
                dsObj = hd.GetDataset("select af_row_id,object_id from sy_obj where object_id in " + temp);
                dsPage = hd.GetDataset("select page_name,object_id from sy_pg where object_id in " + temp);
            }
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        //dsObj.Tables[0].Select("object_id='" + dr["object_id"].ToString() + "'")[0]["af_row_id"].ToString();
                        //dsPage.Tables[0].Select("object_id='" + dr["object_id"].ToString() + "'")[0]["page_name"].ToString();
                        //p_simple_import_target_object.Items.Add(new ListItem("test", "test"));
                        p_simple_import_target_object.Items.Add(new ListItem(dsPage.Tables[0].Select("object_id='" + dr["object_id"].ToString() + "'")[0]["page_name"].ToString(), dsObj.Tables[0].Select("object_id='" + dr["object_id"].ToString() + "'")[0]["af_row_id"].ToString()));
                        //p_simple_import_target_object.Items.Add(new ListItem(dsPage.Tables[0].Select("object_id='" + dr["object_id"].ToString() + "'")[0]["page_name"].ToString(), dr["object_id"].ToString()));
                    }
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in messages.aspx:loadMasterValues: " + e.Message);
        }
        finally
        {
            ds = null;
            dsPage = null;
            dsObj = null;
            hd = null;
        }
    }
    private string getDateTimeValue(string inp)
    {
        try
        {
            DynamicDisplayI DDI = new DynamicDisplayI();
            string sTemp = DDI.getUsersDateTime(inp);
            if (sTemp == "") sTemp = inp;
            DateTime dt = DateTime.Parse(sTemp);
            return dt.ToString("MM-dd-yyyy HH:mm:ss");
        }
        catch (Exception e)
        {
            return inp;
        }
    }
    private void loadDynamicControls()
    {
        //if (IsPostBack) return;
        HiddenField hf = new HiddenField();
        hf.ID = "hf_ref_url";
        hf.EnableViewState = true;
        if (refererURL.Length > 0) hf.Value = refererURL;
        lbl_message.Controls.Add(hf);
    }

    private void loadRecurrTypes()
    {

    }
    private void loadCategory()
    {

    }
    private bool CheckEditOperation()
    {
        bool blnStatus = true;
        string strPageKey = "";
        bool IsEdit = false;
        if (Page.IsPostBack == false)
        {
            if (Request.QueryString["PK"] != null)
            {
                strPageKey = Request.QueryString["PK"].ToString();
                IsEdit = true;
            }
            if (IsEdit)
            {
                Master MA = new Master();
                blnStatus = MA.EditOperation(dsePageInfo, frmObj, strPageKey);
                if (blnStatus == false)
                {
                    strErrMessage = MA.ErrorMessage;
                    Response.Write("<font color='red'>Error : " + strErrMessage + "</font>");
                }
            }
        }
        if (Request.QueryString["CPY"] != null)
        {
            if (Request.QueryString["CPY"] == "0")
            {
                btnSave.Visible = true;
                btnUpdate.Visible = false;
            }
        }
        else if (Request.QueryString["PK"] == null)
        {
            btnSave.Visible = true;
            btnUpdate.Visible = false;
        }
        else
        {
            btnUpdate.Visible = true;
            btnSave.Visible = false;
        }
        return blnStatus;
    }



    private bool SaveData()
    {
        bool blnStatus = true;
        ScreenReader SR = new ScreenReader();
        dseInputInfo = SR.CaptureData(strPagesetRowID, frmObj, dsePageInfo, true);
        strImportAf_row_id = SR.getHashKey();
        dseInputInfo.Tables[0].Rows[0]["af_row_id"] = strImportAf_row_id;
        p_simple_import_af_row_id.Value = dseInputInfo.Tables[0].Rows[0]["af_row_id"].ToString();
        Master MA = new Master();
        blnStatus = MA.ProcessOperation(dsePageInfo, dseInputInfo, "INSERT");
        if (blnStatus == false)
        {
            strErrMessage = MA.ErrorMessage;
        }
        return blnStatus;
    }

    private bool UpdateData()
    {
        bool blnStatus = true;
        ScreenReader SR = new ScreenReader();
        dseInputInfo = SR.CaptureData(strPagesetRowID, frmObj, dsePageInfo, false);
        Master MA = new Master();
        blnStatus = MA.ProcessOperation(dsePageInfo, dseInputInfo, "UPDATE");
        if (blnStatus == false)
        {
            strErrMessage = MA.ErrorMessage;
        }
        return blnStatus;
    }

    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["Theme"].ToString();
        this.Title = "MaxBlox";
    }
    private bool isInteger(string inp)
    {
        if (inp.Trim().Length == 0) return false;
        try
        {
            int i = Convert.ToInt32(inp);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    private string getHashKey()
    {
        ScreenReader sr = new ScreenReader();
        string ret = sr.getHashKey();
        sr = null;
        return ret;
    }
    private bool isDateTime(string inp)
    {
        if (inp.Trim().Length == 0) return false;
        try
        {
            //30-11-2007 13:53:13
            //DateTime dt = DateTime.Parse(inp);
            DateTime dt = DateTime.ParseExact(inp, dtFormat, null);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    private bool validate()
    {
        try
        {
            //string ret = p_job_schedules_jb_sch_id.Text;            
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    private string uploadFile(bool updateMode, ref string fileName)
    {
        try
        {
            if (FileUpload1.HasFile)
            {
                if (FileUpload1.FileName.Length > 0)
                {
                    int len = FileUpload1.PostedFile.ContentLength;
                    //if (len > 5242880) return "Exceeded the file size limit.";
                    if (len > 4096000) return "Exceeded the file size limit.";
                    byte[] filea = new byte[len];
                    FileUpload1.PostedFile.InputStream.Read(filea, 0, len);

                    fileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
                    string sql = "update sy_simple_import set import_source_file=@pcont"
                                    + ", file_name='" + fileName.Replace("'", "''")
                                    + "', download_url='<a href=''import.aspx?file=" + p_simple_import_af_row_id.Value + "''>" + System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName).Replace("'", "''") + "</a>' " +
                                "where af_row_id='" + p_simple_import_af_row_id.Value + "'";

                    SqlCommand sc = new SqlCommand(sql);
                    SqlParameter sp = new SqlParameter("@pcont", SqlDbType.VarBinary, len);
                    FileUpload1.PostedFile.InputStream.Close();
                    sp.Value = filea;
                    sc.Parameters.Add(sp);
                    HDDatabase hdd = new HDDatabase();
                    hdd.ExecuteCmdObj(sc);
                    hdd = null;
                }
                return "";
            }
            else
            {
                if (updateMode)
                    return "";
                else
                {
                    //delete the record
                    string sqql = "delete from sy_simple_import where af_row_id='" + p_simple_import_af_row_id.Value + "'";
                    HDDatabase hdd = new HDDatabase();
                    hdd.ExecuteCmdObj(new SqlCommand(sqql));
                    p_simple_import_af_row_id.Value = "";
                    hdd = null;
                    return "No File specified to upload.";
                }
            }
        }
        catch (Exception e)
        {
            return e.Message;
        }
    }
    private DataSet extractExcelData(string fileName)
    {
        DataSet dsRet = new DataSet();
        try
        {
            string mConnString = "";
            if (fileName.ToLower().EndsWith(".xlsx") || fileName.ToLower().EndsWith(".xlsm") || fileName.ToLower().EndsWith(".xlsb"))
                mConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
            else
                mConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
            string qry = "select * from [Sheet1$]";
            OleDbConnection mOledbConn = new OleDbConnection(mConnString);
            OleDbCommand mOledbCmd = new OleDbCommand(qry, mOledbConn);
            OleDbDataAdapter mOledbAdapter = new OleDbDataAdapter(mOledbCmd);
            mOledbAdapter.Fill(dsRet, "Table1");
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("import: extractExcelData: Error: " + e.Message);
        }
        return dsRet;
    }

    private DataSet extractCSVData(string fileName)
    {
        DataSet dsRet = new DataSet();
        DataTable Dt = new DataTable();
        try
        {
            Dt = getDataFromFile(fileName);
            if (Dt == null)
                return dsRet;
            dsRet.Tables.Add(Dt);
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("import: extractCSVData: Error: " + e.Message);
        }
        finally
        {
            Dt = null;
        }
        return dsRet;
    }

    private string getFirstLineFromFile(string fileName)
    {
        //retrieves the first line of the text file, used for getting the column names
        //created by sanal on 17 Oct 2006
        string ret = "";
        StreamReader sr = null;
        try
        {
            sr = new StreamReader(fileName);
            ret = sr.ReadToEnd();
            if (ret != string.Empty)
                ret = ret.Substring(0, ret.IndexOf("\r\n"));
            sr.Close();
            sr = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("import: getFirstLineFromFile: Error: " + ex.Message);
            return "";
        }
        finally
        {
            sr = null;
        }
        return ret;
    }

    private string removeStringFromString(string inpString, string removeString)
    {
        //this will remove the specified string from the right and left sides of the input string
        if (inpString.Length < 1) return inpString;
        return inpString.TrimStart(removeString.ToCharArray()).TrimEnd(removeString.ToCharArray());
    }

    private ArrayList getColumnNamesFromFile(string FileName)
    {
        //this will get the CloumnNames from the file as per the specied parameters
        //created by sanal on 17 Oct 2006
        string firstLine = getFirstLineFromFile(FileName);

        ArrayList colNames = new ArrayList();
        try
        {
            string[] cols = firstLine.Split(',');
            cols = handleTextQualifier(cols, ',', "\"");//included by sanal on 05th july 2007 to handle text qualifiers
            for (int i = 0; i < cols.Length; i++)
            {
                string cName = removeStringFromString(cols[i].ToString().Trim(), "");
                if (cName.Trim() != "")
                    colNames.Add(cName);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("import: getColumnNamesFromFile: Error: " + ex.Message);
        }
        return colNames;
    }

    private string[] handleTextQualifier(string[] contentArray, char colDelimter, string textQualifier)
    {
        //created by sanal on 06th july 2007
        //This function will check for the existence of the the column delimter in the data and return the data in the appropriate order.
        if (textQualifier == "") return contentArray;
        ArrayList alTemp = new ArrayList(); string temp = ""; bool firstIteration = true;
        int count = 0;//int addValue=0;
        try
        {
            while (count < contentArray.Length)
            {
                temp = ""; firstIteration = true;
                if (!contentArray[count].StartsWith(textQualifier))
                {
                    alTemp.Add(contentArray[count]);
                    count++;
                    continue;
                }
                //starts with text qualifier
                if (contentArray[count].EndsWith(textQualifier) && contentArray[count].Length > 1)
                {
                    alTemp.Add(contentArray[count]);
                    count++;
                    continue;
                }
                //starts with text qualifier, but not ends with text qualifier
                for (int i = count; i < contentArray.Length; i++)
                {
                    count++;
                    temp = temp + contentArray[i] + colDelimter.ToString();
                    if (contentArray[i].EndsWith(textQualifier) && firstIteration == false) break;
                    firstIteration = false;
                }
                if (!temp.StartsWith(textQualifier))
                    alTemp.Add(textQualifier + temp.TrimEnd(colDelimter) + textQualifier);
                else
                    alTemp.Add(temp.TrimEnd(colDelimter));
            }
            return (string[])alTemp.ToArray(Type.GetType("System.String"));
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("import: getColumnNamesFromFile: Error: " + e.Message);
            return contentArray;
        }
    }

    private string getFileNameWithoutExtension(string fName)
    {
        //this will retrieve only the file name excluding the extension
        //created by sanal on 17 Oct 2006
        string ret = "";
        FileInfo fi = null;
        try
        {
            fi = new FileInfo(fName);
            ret = fi.Name.Replace(fi.Extension, "");
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("import: getFileNameWithoutExtension: Error: " + ex.Message);
        }
        fi = null;
        return ret;
    }

    private string getLineByLine(ref StreamReader reducingContent)
    {
        //used to get the content of the text line by line as per the row delimter
        //created by sanal on 17 Oct 2006
        string ret = "";
        try
        {
            while (ret.IndexOf("\r\n") < 0)
            {
                ret = ret + Convert.ToChar(reducingContent.Read()).ToString();
            }
            ret = ret.TrimEnd("\r\n".ToCharArray());
        }
        catch (Exception ex)
        {
            ret = reducingContent.ReadToEnd();
            if (ret == "")
                return null;
            return ret;
        }
        return ret;
    }

    private DataTable getDataFromFile(string FileName)
    {
        DataTable dtExtract = null;
        StreamReader content = null;
        ArrayList columnNames = new ArrayList();
        try
        {
            content = new StreamReader(FileName);

            columnNames = getColumnNamesFromFile(FileName);
            //create coumns in data table
            dtExtract = new DataTable(getFileNameWithoutExtension(FileName));

            for (int i = 0; i < columnNames.Count; i++)
            {
                DataColumn tmpCol1 = new DataColumn(columnNames[i].ToString().Trim());
                dtExtract.Columns.Add(tmpCol1);
            }

            string line = getLineByLine(ref content);
            line = getLineByLine(ref content);
            while (line != null)
            {
                DataRow drTmp = dtExtract.NewRow();
                string[] cols = line.Split(',');
                cols = handleTextQualifier(cols, ',', "\"");//included by sanal on 05th july 2007 to handle text qualifiers
                for (int i = 0; i < cols.Length; i++)
                {
                    drTmp[i] = cols[i].ToString().Trim();
                }
                dtExtract.Rows.Add(drTmp);
                line = getLineByLine(ref content);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("import: getDataFromFile: Error: " + ex.Message);
        }
        content.Close();
        content = null;
        return dtExtract;
    }


    private DataSet extractMBObjectSchema(ref HDDatabase hdImport, string targetObject, ref string tableName)
    {
        DataSet dsRet = new DataSet();
        tableName = hdImport.GetColumnValue("select object_table from sy_obj where af_row_id='" + targetObject + "'");
        if (tableName.Length > 0)
        {
            dsRet = hdImport.GetDataset("select * from " + tableName + " where 1<>1");
        }
        return dsRet;
    }
    private string getScript(DataRow dr, string tableName)
    {
        string ret = "";
        string temp = "";
        //if (chkIncludeCompanyId.Checked) temp  = "$$COMPANYID$$.";
        //if (chkIncludeCompanyId.Checked && txtCompany.Text.Trim().Length > 0) temp = "[" + txtCompany.Text + "].";
        ret = "insert into " + temp + "[" + tableName + "] (";
        foreach (DataColumn dc in dr.Table.Columns)
        {
            ret = ret + "[" + dc.ColumnName + "]" + ",";
        }
        ret = ret.TrimEnd(',') + ") values (";
        foreach (DataColumn dc in dr.Table.Columns)
        {
            switch (dc.DataType.ToString().ToLower())
            {
                case "system.string":
                case "system.datetime":
                    ret = ret + makeProperData(dr[dc.ColumnName], true) + ",";
                    break;
                case "system.boolean":
                    //ret = ret + "'" + makeProperData(dr[dc.ColumnName]) + "'" + ",";
                    //ret = ret + makeProperData(dr[dc.ColumnName],true) + ",";
                    ret = ret + makeDateTimeData(dr[dc.ColumnName]) + ",";
                    break;
                default:
                    ret = ret + makeProperData(dr[dc.ColumnName], false) + ",";
                    break;
            }
        }
        ret = ret.TrimEnd(',') + ");";
        return ret;
    }
    private string makeProperData(string inp)
    {
        return inp.Replace("'", "''");
    }
    private string makeProperData(object inp)
    {
        if (inp == System.DBNull.Value)
            return "null";
        else
            return inp.ToString().Replace("'", "''");
    }
    public string makeProperData(object inp, bool addQuotes)
    {
        if (inp == System.DBNull.Value)
            return "null";
        else
        {
            if (addQuotes)
                return "'" + inp.ToString().Replace("'", "''") + "'";
            else
                return inp.ToString().Replace("'", "''");
        }
    }
    public string makeDateTimeData(object inp)
    {
        //for sql server 2000 compatibility
        if (inp == System.DBNull.Value)
            return "null";
        else
        {
            return inp.ToString().ToLower() == "true" ? "1" : "0";
        }
    }

    private double convert2Double(object input)
    {
        try
        {
            if (input == "") return 0;
            return Convert.ToDouble(input);
        }
        catch (Exception e)
        {
            return 0;
        }
    }

    private void importData(DataSet dsSource, DataSet dsTarget, string targetTable, ref HDDatabase hdImport)
    {
        if (dsSource == null) return;
        if (dsTarget == null) return;
        if (dsSource.Tables == null) return;
        if (dsTarget.Tables == null) return;
        if (dsSource.Tables.Count < 1) return;
        if (dsTarget.Tables.Count < 1) return;
        DataTable dt = new DataTable(); object tempValue = ""; string strInsertKeys = "";
        bool newDataExists = false;
        bool containsOwnerIDColumn = CheckForOwnerIDColumn(dsSource); // Added by 'Imran Gani' on 11-Feb-2013, to check for owner id field before changes.
        updateDataSet(ref dsSource, targetTable); // Added by srivatsan on Apr 24 2009 for updating Bind and Master detail fields
        HandleDuplicateRecords(dsSource, targetTable); // Added by 'Imran Gani' on 30-Jul-2013, to handle duplicate records from import.
        foreach (DataRow drSource in dsSource.Tables[0].Rows)
        {
            DataRow drTarget = dsTarget.Tables[0].NewRow();
            foreach (DataColumn dcTarget in dsTarget.Tables[0].Columns)
            {
                tempValue = System.DBNull.Value;
                switch (dcTarget.ColumnName.ToLower())
                {
                    case "af_row_id":
                        tempValue = getHashKey();
                        strInsertKeys += tempValue + ";";
                        break;
                    case "created_by":
                        tempValue = HttpContext.Current.Session["user_afrowid"].ToString();
                        break;
                    case "owner_id": // Added by srivatsan on June 15 2009 for owner id field
                        //if (drSource.Table.Columns.Contains("owner_id")) // Commented by 'Imran Gani' on 11-Feb-2013.
                        if (containsOwnerIDColumn) // Added by 'Imran Gani' on 11-Feb-2013, to check for owner id field before changes.
                        {
                            if (drSource[dcTarget.ColumnName].ToString() == "")
                            {
                                //tempValue = HttpContext.Current.Session["user_afrowid"].ToString(); // commented for no owner changes
                            }
                            else
                            {
                                tempValue = drSource[dcTarget.ColumnName].ToString();
                            }
                        }
                        else
                        {
                            tempValue = HttpContext.Current.Session["user_afrowid"].ToString();
                        }
                        break;
                    case "created_at":
                        tempValue = System.DateTime.Now.ToUniversalTime().ToString();
                        break;
                    case "modified_by":
                        tempValue = HttpContext.Current.Session["user_afrowid"].ToString();
                        break;
                    case "modified_at":
                        tempValue = System.DateTime.Now.ToUniversalTime().ToString();
                        break;
                    default:
                        if (drSource.Table.Columns.Contains(dcTarget.ColumnName))
                        {
                            newDataExists = true;
                            object tmp = drSource[dcTarget.ColumnName];
                            if (dcTarget.DataType.FullName == "System.Double" || dcTarget.DataType.FullName == "System.Decimal" || dcTarget.DataType.FullName == "System.Int32")
                                tmp = MBLocale.MBLocalization.getNumericValue(HttpContext.Current.Session["userlocale"].ToString(), tmp); // Added by 'Imran Gani' on 28-Feb-2014, for MBLocale
                            #region Old code commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
                            //{
                            //    tmp = tmp.ToString().Replace(",", "").Replace("$", "").Replace("\"", "").Replace("'", "");
                            //    tmp = convert2Double(tmp);
                            //}
                            #endregion
                            //code is added by Sampath to handle boolean values in import on 27-Jan-2012
                            else if (dcTarget.DataType.FullName == "System.Boolean")
                            {
                                if (tmp == null || tmp == System.DBNull.Value)
                                    tmp = false;
                                else if (tmp.ToString().Trim() == "")
                                    tmp = false;
                            }

                            tempValue = tmp;
                        }
                        break;
                }
                drTarget[dcTarget] = tempValue;
            }
            dsTarget.Tables[0].Rows.Add(drTarget);
        }
        StringBuilder sb = new StringBuilder();
        if (newDataExists)
        {
            //LogImportStart(strImportAf_row_id, dsSource, p_simple_import_target_object.SelectedValue); // Added by Srivatsan on May 25 2010 for Logging Import Statistics // commented by 'Imran Gani' on 30-Jul-2013, to log before this funcion called.
            if (dsTarget.Tables[0].Rows.Count > 0)
            {
                string sQuery = "select * from " + targetTable + " where 1<>1;"; // Added by 'Imran Gani' on 21/03/2012, to pass the string as parameter.
                SaveTable(dsTarget, sQuery, targetTable); // Added by 'Imran Gani' on 21/03/2012, to save the DataTable into DataBase of required Table.
                //SqlCommand sc = new SqlCommand("select * from " + tableName + " where 1<>1");
                //SqlDataAdapter sda = new SqlDataAdapter("select * from " + targetTable + " where 1<>1", hdImport.GetConnection());
                //SqlCommandBuilder scb = new SqlCommandBuilder(sda);
                ////scb.UnquoteIdentifier
                ////scb.QuoteIdentifier = "[";
                ////scb.UnquoteIdentifier = "]";                
                //sda.InsertCommand = scb.GetInsertCommand();
                //sda.ContinueUpdateOnError = true;
                //sda.RowUpdated += new SqlRowUpdatedEventHandler(sda_RowUpdated);
                //int rw = sda.Update(dsTarget.Tables[0]);
                //foreach (DataRow dr in dsTarget.Tables[0].Rows)
                //{
                //    sb.Append(getScript(dr, targetTable));
                //    //sb.Append("\r\n\r\n");
                //    //records++;
                //}
                //int output = hdImport.ExecuteCmdObj(new SqlCommand(sb.ToString()));
                //SqlConnection sc = hdImport.GetConnection();
                //SqlBulkCopy sbc = new SqlBulkCopy(sc);                
                //sbc.DestinationTableName = targetTable;                
                //sc.Open();                
                ////sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
                //sbc.WriteToServer(dsTarget.Tables[0]);
                //sc.Close();                
                //sbc.Close();
                lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/info.gif\" alt=\"info\"></img>&nbsp;" + htRecordsInserted[targetTable].ToString() + " out of " + htRecordsInserted["totalrecords"].ToString() + " records imported.", false); // Modified by 'Imran Gani' on 30-Jul-2013, to display total no. of records.
                lblStatistics.Text = appendTable(errors.ToString());
                if (errors.ToString().Length > 0) setErrorTRVisible(true);
            }
        }
        else if ((int)htRecordsInserted["totalrecords"] == (int)htRecordsInserted["duplicates"]) // Added by 'Imran Gani' on 30-Jul-2013, for duplicates message.
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/info.gif\" alt=\"info\"></img>&nbsp; 0 out of " + htRecordsInserted["totalrecords"].ToString() + " records imported. Record(s) already exists.", false);
        else
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"info\"></img>&nbsp;Unable to find data to import. This may be due to the incorrect headers in the import file.", true);
        UpdateImportStatistics(strImportAf_row_id, (htRecordsInserted[targetTable] == null ? 0 : (int)htRecordsInserted[targetTable]), targetTable, strInsertKeys, errors.ToString(), "Completed");
    }
    private void setErrorTRVisible(bool flag)
    {
        try
        {
            DynamicDisplayI ddi = new DynamicDisplayI();
            //Control cc = ddi.FindControl(this.Controls, "ctl00_ContentPlaceHolder3_errorTr");
            Control cc = ddi.FindControl(this.Controls, "errorTr");
            if (cc != null)
            {
                HtmlTableRow htr = (HtmlTableRow)cc;
                htr.Style["display"] = flag == true ? "block" : "none";
            }
            ddi = null;
        }
        catch (Exception ee)
        {
        }
    }
    private string appendTable(string inp)
    {
        if (inp.Length < 1) return "";
        string ret = "";
        //border:solid 1px #000000
        ret = "<table width='100%' border='0' cellpadding='2' cellspacing='0' class='defaultFont' style='font-size:X-Small;border-bottom:solid 1px #C0C0C0;border-right:solid 1px #C0C0C0;'>";
        ret += "<tr class='Dynamic_PageHeaderRow'><td style='border-left:solid 1px #C0C0C0;border-top:solid 1px #C0C0C0;'>&nbsp;<b>Record Count</b></td><td style='border-left:solid 1px #C0C0C0;border-top:solid 1px #C0C0C0;'>&nbsp;Error Message</td></tr>";
        ret += inp;
        ret += "</table>";
        return ret;
    }
    void sda_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
    {
        recordCount++;
        if (e.Status == UpdateStatus.ErrorsOccurred)
            errors.Append("<tr><td style='border-left:solid 1px #C0C0C0;border-top:solid 1px #C0C0C0;'>&nbsp;" + recordCount.ToString() + "</td><td style='border-left:solid 1px #C0C0C0;border-top:solid 1px #C0C0C0;'>&nbsp;" + e.Errors.Message + "</td></tr>");
        recordsAffected += e.RecordsAffected;
        //throw new Exception("The method or operation is not implemented.");
    }
 
    void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
    {

        throw new Exception("The method or operation is not implemented.");
    }
    private void createFolder()
    {
        try
        {
            if (!Directory.Exists(HttpContext.Current.Server.MapPath(".") + "\\ImportFiles"))
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath(".") + "\\ImportFiles");
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("import: createFolder: Error: " + e.Message);
        }
    }
    private void deleteFile(string fileName)
    {
        try
        {
            if (File.Exists(fileName))
                File.Delete(fileName);
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("import: deleteFile: Error: " + e.Message);
        }
    }
    private string doImport(string targetObject, string file)
    {
        HDDatabase HDD = new HDDatabase();
        //StreamWriter sw = new StreamWriter(@"C:\12345.xlx");
        //BinaryWriter bw = new BinaryWriter(sw);
        SqlCommand sc = null;

        createFolder();
        string ret = ""; string fileName = HttpContext.Current.Server.MapPath(".") + "\\ImportFiles\\" + p_simple_import_af_row_id.Value + "." + file.Substring(file.LastIndexOf(".") + 1);
        HDDatabase hddImport = new HDDatabase();
        try
        {
            string sql = "select import_source_file from sy_simple_import where af_row_id='" + p_simple_import_af_row_id.Value + "'";
            sc = new SqlCommand(sql);
            sc.Connection = hddImport.GetConnection();
            SqlDataReader reader = null;
            sc.Connection.Open();
            reader = sc.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    if (reader.IsDBNull(0))
                    {
                        ret = "no import source file found";
                    }
                    else
                    {
                        SqlBytes bytes = reader.GetSqlBytes(0);
                        //BinaryReader br = new BinaryReader(bytes.Stream);
                        //br.ReadBytes(bytes.
                        //StreamWriter sw = new StreamWriter(fileName);

                        BinaryWriter bw = new BinaryWriter(File.Open(fileName, FileMode.Create));
                        byte[] bContent = new byte[bytes.Stream.Length];
                        bytes.Stream.Read(bContent, 0, (int)bytes.Stream.Length);
                        bw.Write(bContent);
                        bw.Flush();
                        bw.Close();
                        bw = null;

                        DataSet dsSource = null;
                        if (fileName.ToLower().EndsWith(".csv"))
                            dsSource = extractCSVData(fileName);
                        else
                            dsSource = extractExcelData(fileName);

                        //added by Sampath on 10-Nov-2011 for Encoding
                        //CommonFunctions.encodeData(dsSource);

                        if (dsSource == null)
                        {
                            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"error\"></img>&nbsp;Unable to retrieve import data", true);
                            break;
                        }
                        if (dsSource.Tables.Count < 1)
                        {
                            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"error\"></img>&nbsp;Unable to retrieve import data", true);
                            break;
                        }
                        if (dsSource.Tables[0].Rows.Count < 1)
                        {
                            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"error\"></img>&nbsp;No source data to import", true);
                            break;
                        }
                        string sExpectedFld = string.Empty;
                        if (!checkNullForUniqueFlds(dsSource.Tables[0], ref sExpectedFld))
                        {
                            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"error\"></img>&nbsp;The required field <b>" + sExpectedFld + "</b> has one or more empty values. Provide values and import again.", true);
                            break;
                        }
                        string table = "";
                        DataSet dsTarget = extractMBObjectSchema(ref hddImport, targetObject, ref table);
                        LogImportStart(strImportAf_row_id, dsSource, p_simple_import_target_object.SelectedValue); // Added / Re-placed by 'Imran Gani' on 30-Jul-2013, for Logging Import Statistics
                        //Code change is made by Bala for check events [Regarding of importing rep functionalities] (04Jan2012)
                        //BI-before insert, AI-After Insert
                        loadInvokeMethod(targetObject, dsSource, dsTarget, table, ref hddImport, "BI");
                        importData(dsSource, dsTarget, table, ref hddImport);
                        loadInvokeMethod(targetObject, dsSource, dsTarget, table, ref hddImport, "AI");
                        //using (Bitmap productImage = new Bitmap(bytes.Stream))
                        //{
                        //    //String fileName = filePath + photoName;

                        //    // Save in gif format.
                        //    productImage.Save(fileName);
                        //}
                    }
                }
            }

            else
            {
                //Console.WriteLine("No records returned.");
                ret = "no import source file found";
            }
            //return ret;
        }
        catch (Exception ee)
        {
            ret = ee.Message;
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Error Occured." + ee.Message, true);
        }
        finally
        {
            hddImport = null;
            deleteFile(fileName);
            if (sc.Connection != null)
            {
                if (sc.Connection.State != ConnectionState.Closed)
                {
                    sc.Connection.Close();
                }
                sc = null;
            }
        }
        return ret;
    }
    //Added by bala for checking the events of particular object(4Jan2012)
    public bool checkEventExists(string targetObject)
    {
        string pageEvent = "";
        bool ret = false;
        try
        {
            HDDatabase HDD = new HDDatabase();
            string pgAfrowid = HDD.GetColumnValue("select af_row_id from sy_pg where [object_id]='(select object_id from sy_obj where af_row_id='" + targetObject + "')'");
            pageEvent = HDD.GetColumnValue("select page_event from sy_pg_evnt where page_id='" + pgAfrowid + "'");
            if (pageEvent.Trim() != null && pageEvent.Trim() != "")
                ret = true;
        }
        catch (Exception ex)
        {
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"error\"></img>&nbsp;Error Occured.<br>" + ex.Message, true);
        }
        return ret;
    }
    // This method is added by bala for get the Event name [Regarding of importing rep functionalities](4Jan2012)
    public DataTable GetpageEvent(string targetObject, string mode)
    {
        DataTable dtEvent = new DataTable();
        HDDatabase HDD = new HDDatabase();
        if (mode == "BI")
        {
            mode = "'PL','PS','PU'";
        }
        else
        {
            mode = "'PAS','PAU'";
        }
        try
        {
            string pgAfrowid = HDD.GetColumnValue("select af_row_id from sy_pg where [page_table_name]=(select object_table from sy_obj where af_row_id='" + targetObject + "')");
            dtEvent = HDD.GetDataTable("select * from sy_pg_evnt where page_id='" + pgAfrowid + "' and page_event in (" + mode + ")");

        }
        catch (Exception ex)
        {
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Error Occured.<br>" + ex.Message, true);
        }
        finally
        {
            HDD = null;
        }
        return dtEvent;
    }


    // This method is added by bala for calling invokeEvent[Regarding of importing rep functionalities](4Jan2012)
    public void loadInvokeMethod(string targetObject, DataSet dsSource, DataSet dsTarget, string targetTable, ref HDDatabase hdImport, string mode)
    {
        try
        {
            string sFunctionName = "";
            DataTable dtPageInfo = new DataTable();
            Hashtable hsAssignControls = new Hashtable();
            DataRow[] drSource = dsTarget.Tables[0].Select("1=1");
            DataTable dtEvent = GetpageEvent(targetObject, mode);
            dtPageInfo = getPageID(targetObject);
            foreach (DataRow dr in dtEvent.Rows)
            {

                invokeEvent(dtPageInfo, ref sFunctionName, ref hsAssignControls, dr, drSource);

            }

        }
        catch (Exception Ex)
        {
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Error Occured.<br>" + Ex.Message, true);
        }

    }
    private DataTable getPageID(string targetObject)
    {
        HDDatabase HDD = new HDDatabase();
        DataTable dtPageId = new DataTable();
        try
        {
            dtPageId = HDD.GetDataTable("select page_id from sy_pg where [page_table_name]=(select object_table from sy_obj where af_row_id='" + targetObject + "')");

        }
        catch (Exception Ex)
        {
            lblStatusUpload.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Error Occured.<br>" + Ex.Message, true);
        }
        finally
        {
            HDD = null;
        }
        return dtPageId;
    }
    // This method is added by bala for calling runMethod[Regarding of importing rep functionalities](4Jan2012)
    private void invokeEvent(DataTable PageInfo, ref string FunctionName, ref Hashtable hsAssignControls, DataRow drEventInfo, DataRow[] drSource)
    {
        HDDatabase HDD = new HDDatabase();
        DataRow[] drParams = null;
        DataTable dtParams = null;
        if (drEventInfo["Event_Type"].ToString() == "ad")
        {
            string sEventCode = "";
            switch (drEventInfo["page_event"].ToString().ToUpper())
            {
                case "PL":
                    sEventCode = "LOAD";
                    break;
                case "PS":
                    sEventCode = "INSERT";
                    break;
                case "PU":
                    sEventCode = "UPDATE";
                    break;
                case "PAS":
                    sEventCode = "AFTER_INSERT";
                    break;
                case "PAU":
                    sEventCode = "AFTER_UPDATE";
                    break;
            }

            DataRow drFunction = HDD.GetTableRow("select * from sy_page_events_advanced where af_row_id = '" + drEventInfo["event_function"].ToString() + "'");
            runMethod(HttpContext.Current.Server.MapPath(".") + "\\CustomFunctions\\" + drFunction["dll_name"].ToString().Trim() + ".dll", drFunction["namespace"].ToString().Trim() + "." + drFunction["class"].ToString().Trim(), drFunction["function_name"].ToString().Trim(), sEventCode, drSource);
            drFunction = null;
        }
        else
        {
            executeEvent(getEvent(drEventInfo["Event_Function"].ToString(), ref FunctionName, ref drParams, ref dtParams), ref drParams, ref dtParams, PageInfo, ref FunctionName, drEventInfo["Event_Type"].ToString(), ref hsAssignControls, drEventInfo);
        }
    }
  //Function added by Bala on 10FEb2012 for calling advanced functions in the delete operation.
    private void invokeEvent(DataTable dtPageInfo, string strOpr, string strPageKeys,  DataRow drPageEvent, string tablename, string sEvent)
    {
        HDDatabase HDD = new HDDatabase();
        try
        {
            DataRow[] drSource = dtPageInfo.Select("1=1");
            DataRow drFunction = HDD.GetTableRow("select * from sy_page_events_advanced where af_row_id = '" + drPageEvent["event_function"].ToString() + "'");
            runMethod(HttpContext.Current.Server.MapPath(".") + "\\CustomFunctions\\" + drFunction["dll_name"].ToString().Trim() + ".dll", drFunction["namespace"].ToString().Trim() + "." + drFunction["class"].ToString().Trim(), drFunction["function_name"].ToString().Trim(), sEvent, drSource);
            drFunction = null;
            drSource = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("invokeEvent : error : " + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        
    }

    //private void eventcall(DataSet dsSource)
    //{
    //    string ret = "";
    //    try
    //    {
    //        HDDatabase HDD = new HDDatabase();
    //        string drEventInfo = "2e6f2ef359bc73c0";
    //        string Event_Type = "ad";
    //        object oRet = null;
    //        string sEventCode = "";
    //        sEventCode = "AFTER_INSERT";
    //        //DataRow[] drFunction = HDD.GetDataTable("select * from sy_page_events_advanced where af_row_id = '" + drEventInfo.ToString() + "'").Select("select * from sy_page_events_advanced where af_row_id = '" + drEventInfo.ToString() + "'");
    //        DataRow drFunction = HDD.GetTableRow("select * from sy_page_events_advanced where af_row_id = '" + drEventInfo.ToString() + "'");

    //        oRet = runMethod(HttpContext.Current.Server.MapPath(".") + "\\CustomFunctions\\" + drFunction["dll_name"].ToString().Trim() + ".dll", drFunction["namespace"].ToString().Trim() + "." + drFunction["class"].ToString().Trim(), drFunction["function_name"].ToString().Trim(), sEventCode, dsSource);
    //        drFunction = null;


    //    }
    //    catch (Exception ee)
    //    {
    //        lblStatusUpload.Text = "Error Occured in Import.<br>" + ee.Message;
    //    }
    //}
    private bool validFile()
    {
        string ext = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower();
        string[] invalids = new string[] { ".xls", ".xlsx", ".xlsm", ".xlsb", ".csv" };
        for (int i = 0; i < invalids.Length; i++)
        {
            if (ext.IndexOf(invalids[i]) > -1)
                return true;
        }
        return false;

    }
    private string wrapItAsMessage(string msg, bool isError)
    {
        return "<div class=\"" + (isError == true ? "mbErrorMessage" : "mbMessage") + "\">" + msg + "</div>";
    }
    protected void btnImport_ServerClick(object sender, EventArgs e)
    {
        HideStatsPanel();
        setErrorTRVisible(false);
        lblStatusUpload.Text = "";
        lbl_message.Text = "";
        errors = new StringBuilder();
        loadObjectFieldsToAvoidDuplicates(p_simple_import_target_object.SelectedValue); // Added by 'Imran Gani' on 23-Aug-2013, to load object related fields.
        getselectedFieldToAvoidDuplicates(); // Added by 'Imran Gani' on 23-Aug-2013, to load selected field of an object to avoid duplicate.
        if (!validate()) return;
        if (!validFile())
        {
            lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Invalid File specified.", true); ;
            p_simple_import_target_object_SelectedIndexChanged(null, null);
            HideStatsPanel();
            return;
        }
        if (SaveData())
        {
            string file = "";
            string ret = uploadFile(false, ref file);
            if (ret != "")
            {
                lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Error occurred during upload. Error: " + ret, true);
                return;
            }
            doImport(p_simple_import_target_object.SelectedValue, file);
            //Response.Write("Data Inserted Successfully");
            //string refURL = getRefURL();
            //            if (refURL.Length > 0)
            //Response.Redirect("im_s2.aspx?PK=" + p_import_temp_af_row_id.Value);
            //          else
            //            lbl_message.Text = "Data Inserted Successfully";
        }
        else
        {
            lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Insert not successful. Error:" + ErrorMessage, true);
        }

        //td_Import_stats.Controls.Clear();
        //LoadStats(p_simple_import_target_object.SelectedValue);
        p_simple_import_target_object_SelectedIndexChanged(null, null);
        HideStatsPanel();


    }
    private string getExpression(ref DataRow dr, DataRow drEventInfo, ref Hashtable hsAssignControl, ref DataTable PageInfo)
    {
        HDDatabase HDD = new HDDatabase();
        string sFunction = "";

        try
        {
            //sFunction = dr["fn_code"].ToString();
            sFunction = dr["fn_code"].ToString();

            string sSql = "Select * from sy_pg where page_id='" + PageInfo.TableName + "'";
            DataRow drPageObject = HDD.GetTableRow(sSql);
            string stemp = drPageObject["multiple_recs_page"].ToString();
            bool is_multi_rec_page = Convert.ToBoolean(stemp == "" ? "false" : stemp);
            string sObjectTable = drPageObject["page_table_name"].ToString();
            string sObjectId = drPageObject["object_id"].ToString();

            if (is_multi_rec_page)
            {
                MSScriptControl.ScriptControl objScriptCtrl = new MSScriptControl.ScriptControl();

                objScriptCtrl.Language = "vbscript";

                string sCtrlId = drEventInfo["event_ass_cont"].ToString();

                for (int iRowCount = 0; iRowCount < PageInfo.Rows.Count; iRowCount++)
                {
                    string sTempExpression = sFunction;
                    DataRow drInfo = PageInfo.Rows[iRowCount];

                    foreach (DataColumn dc in PageInfo.Columns)
                    {
                        string sTempKey = "[" + sObjectId + "." + dc.ColumnName + "]";
                        if (sTempExpression.IndexOf(sTempKey) != -1)
                        {
                            sTempExpression = sTempExpression.Replace(sTempKey, drInfo[dc.ColumnName].ToString());
                        }
                    }

                    object objRet = objScriptCtrl.Eval(sTempExpression);
                    if (!hsAssignControl.Contains(sCtrlId + iRowCount.ToString()))
                        hsAssignControl.Add(sCtrlId + "_" + iRowCount.ToString(), objRet);
                    else
                        hsAssignControl[sCtrlId + "_" + iRowCount.ToString()] = objRet;
                }
            }
            else
            {
                Hashtable hsSysVar = (Hashtable)HttpContext.Current.Session["SysVar"];
                if (hsSysVar != null)//if (GlobalValues.hsSysVar != null)
                {
                    foreach (string sKey in hsSysVar.Keys)//foreach (string sKey in GlobalValues.hsSysVar.Keys)
                    {
                        if (sKey.Contains("."))
                        {
                            /*string sObjectTable = sKey.Substring(0, sKey.IndexOf("."));
                            sSql = "Select object_id from sy_obj where object_table='" + sObjectTable.Replace("'", "''") + "'";
                            string sObjectId = HDD.GetColumnValue(sSql);*/
                            /*if (is_multi_rec_page)
                            {
                                MSScriptControl.ScriptControl objScriptCtrl = new MSScriptControl.ScriptControl();

                                objScriptCtrl.Language = "vbscript";

                                string sCtrlId = drEventInfo["event_ass_cont"].ToString();
                                for (int iRowCount = 0; iRowCount < PageInfo.Rows.Count; iRowCount++)
                                {
                                    DataRow drInfo = PageInfo.Rows[iRowCount];

                                    string sTempKey = "[" + sKey.Replace(sObjectTable, sObjectId) + "]";
                                    if (sFunction.IndexOf(sTempKey) != -1)
                                    {
                                        sFunction = sFunction.Replace(sTempKey, GlobalValues.hsSysVar[sKey].ToString());
                                    }

                                    object objRet = objScriptCtrl.Eval(sFunction);
                                    if (!hsAssignControl.Contains(sCtrlId + iRowCount.ToString()))
                                        hsAssignControl.Add(sCtrlId + "_" + iRowCount.ToString(), objRet);
                                    else
                                        hsAssignControl[sCtrlId + "_" + iRowCount.ToString()] = objRet;
                                }
                            }
                            else
                            {*/
                            string sTempKey = "[" + sKey.Replace(sObjectTable, sObjectId) + "]";
                            if (sFunction.IndexOf(sTempKey) != -1)
                            {
                                sFunction = sFunction.Replace(sTempKey, hsSysVar[sKey].ToString());//sFunction = sFunction.Replace(sTempKey, GlobalValues.hsSysVar[sKey].ToString());
                            }
                            //}
                        }
                    }
                }
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("getExpression : error : " + ex.Message);
        }
        return sFunction;
    }
    // This method is added by bala for calling [Regarding of importing rep functionalities](4Jan2012)
    private object executeEvent(DataRow dr, ref DataRow[] Parameters, ref DataTable dtParams, DataTable PageInfo, ref string FunctionName, string Event_Type, ref Hashtable hsAssignControls, DataRow drEventInfo)
    {
        HDDatabase HDD = new HDDatabase();
        object objRet = null;
        try
        {
            FunctionName = "The Event " + FunctionName;
            if (dr != null)
            {
                MSScriptControl.ScriptControl objScriptCtrl = new MSScriptControl.ScriptControl();

                objScriptCtrl.Language = "vbscript";

                if (dr["fn_type"].ToString().ToLower() == "expression")
                {
                    string sExpression = getExpression(ref dr, drEventInfo, ref hsAssignControls, ref PageInfo);

                    objRet = objScriptCtrl.Eval(sExpression);
                }
                else
                {
                    string sFunctionCode = dr["fn_code"].ToString();
                    objScriptCtrl.AddCode(sFunctionCode);
                    foreach (MSScriptControl.Procedure Proc in objScriptCtrl.Procedures)
                    {
                        string sFuncName = Proc.Name;

                        object[] oParams = new object[Proc.NumArgs];
                        if (Proc.NumArgs > 0)
                        {
                            FunctionName = FunctionName + " with parameter(s) :";
                            int iStartPos = sFunctionCode.IndexOf("(", sFunctionCode.ToLower().IndexOf("function " + sFuncName.ToLower())) + 1;
                            int iEndPos = sFunctionCode.IndexOf(")", iStartPos);
                            string[] sTemp = sFunctionCode.Substring(iStartPos, iEndPos - iStartPos).Split(',');
                            for (int iArg = 0; iArg < sTemp.Length; iArg++)
                            {
                                DataRow[] dtTemp = dtParams.Select("param_name='" + sTemp[iArg].Trim() + "'");
                                if (dtTemp.Length > 0)
                                {
                                    string sParam = dtTemp[0]["param_value"].ToString();
                                    FunctionName = FunctionName + " " + sParam + ",";
                                    if (sParam.StartsWith("{!") && sParam.EndsWith("}"))
                                    {
                                        Hashtable hsSysVar = (Hashtable)HttpContext.Current.Session["SysVar"];
                                        if (hsSysVar != null)//if (GlobalValues.hsSysVar != null)
                                        {
                                            if (hsSysVar.Contains(sParam))
                                                oParams[iArg] = hsSysVar[sParam].ToString();
                                            else
                                                oParams[iArg] = "";
                                            //sParam = sParam.Remove(sParam.Length - 1, 1);
                                            //sParam = sParam.Remove(0, 2);
                                            /*if (GlobalValues.hsSysVar.Contains(sParam))
                                                oParams[iArg] = GlobalValues.hsSysVar[sParam].ToString();
                                            else
                                                oParams[iArg] = "";*/
                                        }
                                    }
                                    else if (sParam.StartsWith("{@") && sParam.EndsWith("}"))
                                    {
                                        security hds = new security();
                                        Hashtable hsAppVar = new Hashtable();
                                        hds.loadAppVariables(ref hsAppVar);
                                        hds = null;

                                        if (hsAppVar != null)
                                        {
                                            if (hsAppVar.Contains(sParam))
                                                oParams[iArg] = hsAppVar[sParam].ToString();
                                            else
                                                oParams[iArg] = "";
                                        }

                                        //if (GlobalValues.hsAppVar != null)
                                        //{
                                        //    //sParam = sParam.Remove(sParam.Length - 1, 1);
                                        //    //sParam = sParam.Remove(0, 2);
                                        //    if (GlobalValues.hsAppVar.Contains(sParam))
                                        //        oParams[iArg] = GlobalValues.hsAppVar[sParam].ToString();
                                        //    else
                                        //        oParams[iArg] = "";
                                        //}
                                    }

                                    else if (sParam.StartsWith("[") && sParam.EndsWith("]") && sParam.Contains("."))//("].[")
                                    {
                                        string sObject_id = sParam.Substring(sParam.IndexOf("[") + 1, sParam.IndexOf(".") - (sParam.IndexOf("[") + 1));

                                        string sSql = "Select object_table from sy_obj where object_id='" + sObject_id + "'";
                                        string sObject_table = HDD.GetColumnValue(sSql);
                                        sSql = "Select multiple_recs_page from sy_pg where page_id = 'p_" + sObject_id + "'";
                                        string stemp = HDD.GetColumnValue(sSql);
                                        bool is_multi_rec_page = Convert.ToBoolean(stemp == "" ? "false" : stemp);
                                        sParam = sObject_table + "." + sParam.Substring(sParam.IndexOf(".") + 1, sParam.Length - 1 - (sParam.IndexOf(".") + 1));
                                        //sParam = sParam.Replace("].[", ".");
                                        if (is_multi_rec_page)
                                        {
                                            string sCtrlId = drEventInfo["event_ass_cont"].ToString();
                                            for (int iRowCount = 0; iRowCount < PageInfo.Rows.Count; iRowCount++)
                                            {
                                                DataRow drInfo = PageInfo.Rows[iRowCount];
                                                oParams[iArg] = drInfo[sParam.Substring(sParam.IndexOf(".") + 1, sParam.Length - (sParam.IndexOf(".") + 1))].ToString();
                                                objRet = objScriptCtrl.Run(sFuncName, ref oParams);
                                                //FunctionName = FunctionName + " returned " + objRet.ToString();
                                                if (Event_Type == "v")
                                                {
                                                    if (Convert.ToBoolean(objRet.ToString()) == false)
                                                    {
                                                        FunctionName = FunctionName.Remove(FunctionName.Length - 1);
                                                        return objRet;
                                                    }
                                                }
                                                else if (Event_Type == "a")
                                                {
                                                    if (!hsAssignControls.Contains(sCtrlId + "_" + iRowCount.ToString()))
                                                        hsAssignControls.Add(sCtrlId + "_" + iRowCount.ToString(), objRet);
                                                    else
                                                        hsAssignControls[sCtrlId + "_" + iRowCount.ToString()] = objRet;
                                                }
                                            }
                                        }
                                        else
                                        {
                                            Hashtable hsSysVar = (Hashtable)HttpContext.Current.Session["SysVar"];
                                            if (hsSysVar.Contains(sParam))
                                                oParams[iArg] = hsSysVar[sParam].ToString();
                                            else
                                                oParams[iArg] = "";
                                            /*if (GlobalValues.hsSysVar.Contains(sParam))
                                                oParams[iArg] = GlobalValues.hsSysVar[sParam].ToString();
                                            else
                                                oParams[iArg] = "";*/
                                        }
                                    }
                                    else
                                        oParams[iArg] = sParam;
                                }
                                else
                                    oParams[iArg] = "";
                            }
                            FunctionName = FunctionName.Remove(FunctionName.Length - 1);
                        }

                        objRet = objScriptCtrl.Run(sFuncName, ref oParams);
                        objScriptCtrl = null;
                    }
                }
            }
        }

        catch (Exception ex)
        {
            Master objMaster = new Master();
            LogWriter.WriteLog("executeEvent : Error : " + ex.Message);
            objMaster.DeleteMessage = FunctionName + " terminated execution with the error : " + ex.Message;
            objRet = false;
        }
        finally
        {
        }
        return objRet;
    }
    private DataRow getEvent(string EventFunction, ref string sFunctionName, ref DataRow[] Parameters, ref DataTable Params)
    {
        HDDatabase HDD = new HDDatabase();
        string sSql = "select fr_name from my_function_register where id='" + EventFunction + "'";
        sFunctionName = HDD.GetColumnValue(sSql);

        sSql = "select fr_selected_fnExp from my_function_register where id='" + EventFunction + "'";
        string sTemp = HDD.GetColumnValue(sSql);
        sFunctionName = "'" + sFunctionName + "' (" + sTemp + ")";

        string sFunction_Id = HDD.GetColumnValue("select af_row_id from my_function_register where id='" + EventFunction + "'");

        sSql = "select * from my_fn_register_child where id='" + sFunction_Id + "'";
        DataRow[] drParams = HDD.GetDataTable(sSql).Select();

        sSql = "select * from my_fn_register_child where id ='" + sFunction_Id + "'";
        Params = HDD.GetDataTable(sSql);

        sSql = "select * from my_function where fn_name='" + sTemp + "'";
        return HDD.GetTableRow(sSql);
    }

    // This method was added by bala for invoke the custom functions[Regarding of importing rep functionalities](4Jan2012) 
    private object runMethod(string assemblyFileName, string typeName, string methodName, string Event, DataRow[] drSource)
    {
        DataTable dataTable = new DataTable();
        Object obj;
        object objret = null;
        try
        {
            obj = LoadDLL(assemblyFileName, typeName, null);
            Type myTypeObj = obj.GetType();
            //MethodInfo myMethodInfo = myTypeObj.GetMethod("returnStr");
            System.Reflection.MethodInfo myMethodInfo = myTypeObj.GetMethod(methodName);
            object[] mParam = null;
            if (HttpContext.Current.Request["MK"] == null)
            {
                mParam = new object[] { HttpContext.Current.Session["dbcon"], HttpContext.Current.Session["company_id"], HttpContext.Current.Session["user_afrowid"], HttpContext.Current.Request["mn"], Event, drSource };
            }
            else
            {
                mParam = new object[] { HttpContext.Current.Session["dbcon"], HttpContext.Current.Session["company_id"], HttpContext.Current.Session["user_afrowid"], HttpContext.Current.Request["MK"], HttpContext.Current.Request["mn"], Event };
            }

            if (methodName.ToLower().EndsWith("_ver1"))
            {
                Array.Resize(ref mParam, mParam.Length + 1);
                mParam.SetValue(addAdditionalParam(drSource), mParam.Length - 1);
            }
            try
            {
                object ob = myMethodInfo.Invoke(obj, mParam);
                objret = ob;
            }
            catch (Exception Arg)
            {
                LogWriter.WriteLog("Error Occurred in passing Arguments." + Arg.Message);
            }

            myMethodInfo = null;
            myTypeObj = null;
        }
        catch (Exception e)
        {
            objret = null;
            LogWriter.WriteLog("Error in invoking method: " + methodName + " in assembly " + assemblyFileName + ". \r\nError:\r\n" + e.Message);
        }
        finally
        {
            obj = null;
        }
        return objret;
    }
    private object addAdditionalParam(DataRow[] DR)
    {
        //Function Description
        #region
        /* This function will get the below things and return as Array.
* 1. DataRow
* 2. URL Referrer
* 3. Request URL
*/
        #endregion
        //Declaration Phase
        Hashtable HParam = new Hashtable();
        string RequestURL = "";
        string URLReferer = "";

        try
        {
            if (HttpContext.Current.Request != null)
            {
                if (HttpContext.Current.Request.Url != null)
                {
                    RequestURL = HttpContext.Current.Request.Url.ToString();
                }
                if (HttpContext.Current.Request.UrlReferrer != null)
                {
                    URLReferer = HttpContext.Current.Request.UrlReferrer.ToString();
                }
            }
            HParam.Add("DataRow", DR);
            HParam.Add("RequestURL", RequestURL);
            HParam.Add("URLReferer", URLReferer);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error while getting object:" + ex.Message);
        }
        return (object)HParam;
    }
    public Object LoadDLL(string DLLName, string ClassName, object[] args)
    {
        Object objTmp = null;
        try
        {
            System.Reflection.Assembly objAssembly = System.Reflection.Assembly.LoadFrom(DLLName);
            objTmp = objAssembly.CreateInstance(ClassName);
        }
        catch (Exception ex)
        {
            return null;
        }
        return objTmp;
    }

    protected void btnSave_ServerClick(object sender, EventArgs e)
    {
        if (!validate()) return;
        if (SaveData())
        {
            string file = "";
            string ret = uploadFile(false, ref file);
            if (ret != "")
            {
                lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Error occurred during upload. Error: " + ret, true);
                return;
            }
            doImport(p_simple_import_target_object.SelectedValue, file);
            //Response.Write("Data Inserted Successfully");
            //string refURL = getRefURL();
            //            if (refURL.Length > 0)
            //Response.Redirect("im_s2.aspx?PK=" + p_import_temp_af_row_id.Value);
            //          else
            //            lbl_message.Text = "Data Inserted Successfully";
        }
        else
        {
            lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Insert not successful. Error:" + ErrorMessage, true);
        }
    }
    protected void btnUpdate_ServerClick(object sender, EventArgs e)
    {
        //if (!validate()) return;
        if (!validFile())
        {
            lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Invalid File specified.", true);
            return;
        }
        if (UpdateData())
        {
            string file = "";
            string ret = uploadFile(true, ref file);
            if (ret != "")
            {
                lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Error occurred during upload. Error: " + ret, true);
                return;
            }
            string refURL = getRefURL();
            if (refURL.Length > 0)
                Response.Redirect(refURL);
            else
                lbl_message.Text = wrapItAsMessage("Data Updated Successfully", false);
        }
        else
        {
            lbl_message.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Update not successful. Error:" + ErrorMessage, true);
        }
    }

    protected void btnreset_ServerClick(object sender, EventArgs e)
    {
        string refURL = getRefURL();
        Response.Redirect(refURL);
        //Response.Redirect(Request.RawUrl);
        try
        {
            //if (p_format_masks_type.Items.Count>0)   p_format_masks_type.SelectedIndex = 0;

        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("import: btnreset_ServerClick: Error: " + ee.Message);
        }
    }
    private string getRefURL()
    {
        string ret = "";
        try
        {
            //return hfRequestURL.Value;
            if (HttpContext.Current.Session["URLs"] == null)
                return hfRequestURL.Value;
            else
            {
                return Navigator.getURL(Request.Url.ToString());
            }
            //DynamicDisplayI di = new DynamicDisplayI();
            //HiddenField hf = (HiddenField)di.FindControl(this.Controls, "hf_ref_url");
            //ret = hf.Value;
            //di = null;
            //return ret;
        }
        catch (Exception e)
        {
            return "";
        }

    }

    public string ErrorMessage
    {
        get
        {
            return this.strErrMessage;
        }
    }
    protected void p_simple_import_target_object_SelectedIndexChanged(object sender, EventArgs e)
    {
        //lblXLSLink.Text = "Excel Template is not available.";
        //lblCSVLink.Text = "CSV Template is not available.";
        lblXLSLink.Text = "";
        lblCSVLink.Text = ""; //Change is made by Bala for disable the msg when the link is NotFiniteNumberException having the template-2Mar12
        HDDatabase hdd = new HDDatabase();
        try
        {
            hf_avoid_duplicates_based_on.Value = "";  // Added by 'Imran Gani' on 23-Aug-2013, to reset selected field of an object to avoid duplicate.
            string object_id = hdd.GetColumnValue("select object_id from sy_obj where af_row_id='" + p_simple_import_target_object.SelectedValue + "'");
            if (object_id.Length > 0)
            {
                string sql = "SELECT [link] FROM sy_import_templates where object_id='" + object_id + "'";
                DataTable dt = new DataTable();//code to show the link if the link is specified in the link column
                dt = hdd.GetDataTable(sql);
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["link"] != System.DBNull.Value)
                        {
                            //if (dr["link"].ToString().ToLower().EndsWith(".xls"))
                            //    lblXLSLink.Text = "<font size='2px' face='Arial'><a href='" + dr["link"].ToString() + "'>Download Excel Template(.xls)</a></font>";
                            //else if (dr["link"].ToString().ToLower().EndsWith(".csv"))
                            //    lblCSVLink.Text = "<font size='2px' face='Arial'><a href='" + dr["link"].ToString() + "'>Download CSV Template(.csv)</a></font>";

                            if (dr["link"].ToString().ToLower().EndsWith(".xls"))
                                lblXLSLink.Text = "<a href='" + dr["link"].ToString() + "'>Excel</a>";
                            else if (dr["link"].ToString().ToLower().EndsWith(".csv"))
                                lblCSVLink.Text = "<a href='" + dr["link"].ToString() + "'>CSV</a>";
                        }
                    }
                }
                HideStatsPanel();
            }

        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in p_simple_import_target_object_SelectedIndexChanged function. Error: " + ee.Message);
        }
        finally
        {
            hdd = null;
        }
    }
    private void getpageicons()// Added by srivatsan to get page icon
    {
        PageIcons icons = new PageIcons();
        HDDatabase Hdd = new HDDatabase();
        try
        {
            string strPagesetRowID = Request.QueryString["pgs_id"].ToString();

            lblHeader.Text = "&nbsp;" + icons.geticontextfrompageset(strPagesetRowID, 2) + "&nbsp;" + lblHeader.Text;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error :getpageicons() " + ex.Message);
            lblHeader.Text = "<img src=" + PageIcons.strdefaulticon.ToString() + "></img>&nbsp;" + lblHeader.Text;
        }
        finally
        {
            icons = null;
            Hdd = null;
        }

    }

    private void updateDataSet(ref DataSet ds, string tablename) // Added by srivatsan for Bind and Master detail fields update
    {
        DataSet dsControls = new DataSet();
        try
        {
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        bool containsOwnerIDColumn = CheckForOwnerIDColumn(ds); // Added by 'Imran Gani' on 11-Feb-2013, to check for owner id field before changes.
                        ds = UpdateDataType(ds, tablename);

                        dsControls = getPageInfoForTable(ds, tablename, containsOwnerIDColumn); // Get all the Page related objects //Changes made by 'Imran Gani' on 22/03/12.
                        dsControls.AcceptChanges(); // Added by 'Imran Gani' on 16/03/2012, to set as original dataset before any changes occur.
                        bool autoadd = dsControls.Tables["import_temp"].Rows[0]["autoadd_linked_records"] == System.DBNull.Value ? false : Convert.ToBoolean(dsControls.Tables["import_temp"].Rows[0]["autoadd_linked_records"].ToString());
                        UpdateBindValues(ref ds, ref dsControls, autoadd); //Check for Bind field and update values
                        UpdateDefaultValues(ds.Tables[0], dsControls.Tables["Page Controls"]); //Changes made by 'Imran Gani' on 20/03/12.
                        UpdateEncryptionField(ds.Tables[0], dsControls.Tables["Page Controls"]); // Added by 'Imran Gani' on 10-Jul-2014, to handle encryption fields.
                        if (autoadd) // Added by 'Imran Gani' on 30/03/2012, to check the auto add master record.
                            SaveMasterData(dsControls); // Added by 'Imran Gani' on 16/03/2012, to check the master record.
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error UpdateDataSet for Bind fields :" + ex.Message);
        }
        finally
        {
            dsControls = null;
        }

    }
    


    /// <summary>
    /// To check, is there any master records to be added.
    /// </summary>
    /// <param name="dsControls">DataSet of page Dictionary.</param>
    private void SaveMasterData(DataSet dsControls) // Added by 'Imran Gani' on 20/03/2012.
    {
        DataTable dtChng = new DataTable();
        try
        {
            if (dsControls.Tables.Contains("Page Controls"))
            {
                DataRow[] datarowbindids = dsControls.Tables["Page Controls"].Select("control_bind_id <> ''");
                for (int i = 0; i < datarowbindids.Length; i++)
                {
                    string columnname = datarowbindids[i]["control_attrib_id"].ToString().ToLower();
                    if (columnname != "created_by" && columnname != "created_at" && columnname != "modified_at" && columnname != "modified_by" && columnname != "owner_id")
                    {
                        string datatablename = "bnd_" + datarowbindids[i]["control_bind_id"].ToString();
                        if (dsControls.Tables.Contains(datatablename) && dsControls.Tables[datatablename].Rows.Count > 0)
                        {
                            dtChng = dsControls.Tables[datatablename].GetChanges();
                            if (dtChng != null && dtChng.Rows.Count > 0)
                            {
                                AddMasterData(columnname, dtChng, dsControls); // Added by 'Imran Gani' on 20/03/2012, to add the new master record.
                                dsControls.Tables[datatablename].RejectChanges();
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error SaveMasterData :" + ex.Message);
        }
    }


    /// <summary>
    /// To add a master records from DataSet.
    /// </summary>
    /// <param name="columnname">Name of the Bind Column.</param>
    /// <param name="dtChng">User input DataTable</param>
    /// <param name="dsControls">DataSet of page Dictionary</param>
    private void AddMasterData(string columnname, DataTable dtChng, DataSet dsControls) // Added by 'Imran Gani' on 20/03/2012.
    {
        DataSet dsSource = new DataSet();
        DataSet dsBind = new DataSet();
        HDDatabase hdd = new HDDatabase();
        string sQuery = string.Empty, sTextFld = string.Empty, sDataFld = string.Empty, sTableName = string.Empty, sInsertKeys = string.Empty, targetObject = string.Empty;
        try
        {
            if (dsControls.Tables.Contains("Page Controls") && dsControls.Tables.Contains("bnd_details") && dsControls.Tables.Contains("bnd_access"))
            {
                DataRow[] drBind = null;
                DataRow[] drAcc = null;
                DataRow[] drBindIds = dsControls.Tables["Page Controls"].Select("control_attrib_id = '" + columnname + "'");
                if (drBindIds.Length > 0)
                    drBind = dsControls.Tables["bnd_details"].Select("af_row_id='" + drBindIds[0]["control_bind_id"].ToString() + "'");
                if (drBind.Length > 0)
                    drAcc = dsControls.Tables["bnd_access"].Select("object_table='" + drBind[0]["bind_table"].ToString() + "'");
                if (drBind.Length > 0 && drAcc.Length > 0 && Convert.ToBoolean(drAcc[0]["create"]))
                {
                    sTextFld = drBind[0]["bind_text"].ToString().ToLower();
                    sDataFld = drBind[0]["bind_value"].ToString().ToLower();
                    sTableName = drBind[0]["bind_table"].ToString().ToLower();

                    sQuery = "select * from " + sTableName + " where 1<>1";
                    dsBind = hdd.GetDataset(sQuery);

                    targetObject = drAcc[0]["object_id"].ToString();
                    loadInvokeMethod(targetObject, dsSource, dsBind, sTableName, ref hdd, "BI");

                    foreach (DataRow dr in dtChng.Rows)
                    {
                        DataRow drTarget = dsBind.Tables[0].NewRow();
                        foreach (DataColumn dcTarget in dsBind.Tables[0].Columns)
                        {
                            switch (dcTarget.ColumnName.ToLower())
                            {
                                case "created_by":
                                    drTarget[dcTarget] = HttpContext.Current.Session["user_afrowid"].ToString();
                                    break;
                                case "owner_id":
                                    drTarget[dcTarget] = HttpContext.Current.Session["user_afrowid"].ToString();
                                    break;
                                case "created_at":
                                    drTarget[dcTarget] = System.DateTime.Now.ToUniversalTime().ToString();
                                    break;
                                case "modified_by":
                                    drTarget[dcTarget] = HttpContext.Current.Session["user_afrowid"].ToString();
                                    break;
                                case "modified_at":
                                    drTarget[dcTarget] = System.DateTime.Now.ToUniversalTime().ToString();
                                    break;
                                default:
                                    if (dcTarget.ColumnName.ToString() == "af_row_id" && dcTarget.ColumnName.ToString() != sDataFld)
                                    {
                                        drTarget[dcTarget] = getHashKey();
                                    }
                                    if (dcTarget.ColumnName.ToString() == sDataFld)
                                    {
                                        drTarget[dcTarget] = dr["Val"].ToString();
                                    }
                                    if (dcTarget.ColumnName.ToString() == sTextFld)
                                    {
                                        drTarget[dcTarget] = dr["Fld"].ToString();
                                    }
                                    break;
                            }
                        }
                        sInsertKeys += drTarget["af_row_id"] + ";";
                        dsBind.Tables[0].Rows.Add(drTarget);
                    }
                    if (dsControls.Tables.Contains("bnd_pg_ctr_" + drBind[0]["af_row_id"].ToString()) && dsControls.Tables["bnd_pg_ctr_" + drBind[0]["af_row_id"].ToString()].Rows.Count > 0)
                    {
                        UpdateDefaultValues(dsBind.Tables[0], dsControls.Tables["bnd_pg_ctr_" + drBind[0]["af_row_id"].ToString()]); 
                    }
                    if (dsBind.Tables[0].Rows.Count > 0)
                    {
                        if (sbMasterInserts.ToString().Contains(sTableName))
                            sbMasterInserts.Replace(sTableName + "(", sTableName + "(" + sInsertKeys);
                        else
                            sbMasterInserts.Append("," + sTableName + "(" + sInsertKeys + ")");
                        SaveTable(dsBind, sQuery, sTableName);
                        loadInvokeMethod(targetObject, dsSource, dsBind, sTableName, ref hdd, "AI");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : AddMasterTable :" + ex.Message);
        }
        finally
        {
            hdd = null;
            if (dsBind != null)
                dsBind.Dispose();
        }
    }

    /// <summary>
    /// To Insert the DataSet values into database of required table.
    /// </summary>
    /// <param name="ds">DataSet to be Inserted.</param>
    /// <param name="sQuery">Query for SqlDataAdapter.</param>
    /// <param name="sTableName">Name of the Table to Inserted.</param>
    private void SaveTable(DataSet ds, string sQuery, string sTableName) // Added by 'Imran Gani' on 21/03/2012.
    {
        HDDatabase hdd = new HDDatabase();
        SqlCommandBuilder scb = null;
        SqlDataAdapter sda = null;
        int DefaultBatchSize = 250;
        try
        {
            sda = new SqlDataAdapter(sQuery, hdd.GetConnection());
            scb = new SqlCommandBuilder(sda);
            sda.InsertCommand = scb.GetInsertCommand();
            sda.ContinueUpdateOnError = true;
            sda.RowUpdated += new SqlRowUpdatedEventHandler(sda_RowUpdated);
            try
            {
                sda.UpdateBatchSize = int.Parse(hdd.getAcrMemValue("6fcb183a4781114e", "batch_size"));
            }
            catch (Exception ex)
            {
                sda.UpdateBatchSize = DefaultBatchSize;
                LogWriter.WriteLog("Error : UpdateBatchSize : " + ex.Message);
            }
            sda.Update(ds.Tables[0]);
            htRecordsInserted[sTableName] = recordsAffected;
            recordsAffected = 0;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : SaveTable : " + ex.Message);
        }
        finally
        {
            if (sda != null)
                sda.Dispose();
            if (scb != null)
                scb.Dispose();
        }
    }

    /// <summary>
    /// To Get the default values of required table.
    /// </summary>
    /// <param name="dt">DataTable of source table.</param>
    /// <param name="dtControls">DataSet of Page Dictionary.</param>
    private void UpdateDefaultValues(DataTable dt, DataTable dtControls) //Changes made by 'Imran Gani' on 20/03/12.
    {
        try
        {
            foreach (DataRow aDr in dtControls.Select("default_value IS NOT NULL"))
            {
                string colName = (string)aDr["control_attrib_id"];
                if (dt.Columns.Contains(aDr["control_attrib_id"].ToString()))
                {
                    string expr = colName + "  IS NULL";
                    if (aDr["control_attrib_type"].ToString() == "TX")
                    {
                        expr = colName + " IS NULL OR " + colName + "= ''";
                    }
                    else if (aDr["control_attrib_type"].ToString() == "NU" || aDr["control_attrib_type"].ToString() == "DC")
                    {
                        expr = colName + " IS NULL OR " + colName + "= 0";
                    }

                    foreach (DataRow bDr in dt.Select(expr))
                    {
                        bDr[colName] = aDr["default_value"];
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error UpdateDefaultValues :" + ex.Message);
        }

    }

    //private void UpdateDefaultValues(ref DataSet ds, ref DataSet dsControls)
    //{
    //    try
    //    {
    //        foreach (DataRow aDr in dsControls.Tables["Page Controls"].Select("default_value IS NOT NULL"))
    //        {
    //            string colName = (string)aDr["control_attrib_id"];
    //            if (ds.Tables[0].Columns.Contains(aDr["control_attrib_id"].ToString()))
    //            {
    //                string expr = colName + "  IS NULL";
    //                if (aDr["control_attrib_type"].ToString() == "TX")
    //                {
    //                    expr = colName + " IS NULL OR " + colName + "= ''";
    //                }
    //                else if (aDr["control_attrib_type"].ToString() == "NU" || aDr["control_attrib_type"].ToString() == "DC")
    //                {
    //                    expr = colName + " IS NULL OR " + colName + "= 0";
    //                }

    //                foreach (DataRow bDr in ds.Tables[0].Select(expr))
    //                {
    //                    bDr[colName] = aDr["default_value"];
    //                }
    //            }
    //        }
    //    }
    //    catch (Exception ex)
    //    {
    //        LogWriter.WriteLog("Error UpdateDefaultValues :" + ex.Message);
    //    }

    //}

    private bool CheckForOwnerIDColumn(DataSet ds)
    {
        bool status = false;
        try
        {
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Columns.Count > 0)
                    {
                        if (ds.Tables[0].Columns.Contains("owner_id"))
                        {
                            status = true;
                        }
                    }
                }

            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("import: CheckForOwnerIDColumn: Error: " + e.Message);
        }
        return status;
    }

    private DataSet getPageInfoForTable(DataSet dsSource, string tablename, bool containsOwnerIDColumn) // Get all Page Related tables
    {
        DataSet ds = new DataSet();
        HDDatabase hdd = new HDDatabase();
        DataSet dsControls = new DataSet();
        PageInfoLoad PIL = new PageInfoLoad();
        try
        {
            string sql = "select C.af_row_id from sy_obj A, sy_pgs_mem B,sy_pgs C where A.object_id = B.object_id and B.pageset_id = C.af_row_id and object_table = '" + tablename + "'";
            string strPagesetRowId = hdd.GetColumnValue(sql);
            DataSet dsePageObj = PIL.GetPageSetPagesSrchInfo(strPagesetRowId);
            //dsControls = GetTablesRelatedToPageSetID(strPagesetRowId, dsePageObj, containsOwnerIDColumn); //Commanded by 'Imran Gani' on 22/03/12.
            dsControls = GetBindRelatedTables(dsSource, dsePageObj); // Added by 'Imran Gani' on 22/03/2012, to get the bind related tables. 
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error getPageInfoForTable :" + ex.Message);
            //return dsControls;
        }
        finally
        {
            PIL = null;
            hdd = null;
        }
        return dsControls;
    }

    private void UpdateBindValues(ref DataSet ds, ref DataSet dsControls, bool autoadd) // Update the correct Bind Values in Import Data
    {
        try
        {
            if (dsControls != null)
            {
                if (dsControls.Tables.Count > 0)
                {
                    if (ds != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                try
                                {
                                    foreach (DataColumn dc in ds.Tables[0].Columns)
                                    {
                                        try
                                        {
                                            string columnname = dc.ColumnName.ToLower();
                                            // Below 'if' Added by 'Imran Gani' on 20/03/2012, to ignore system fields for bind updation.
                                            if (columnname != "created_by" && columnname != "created_at" && columnname != "modified_at" && columnname != "modified_by")
                                            {
                                                DataRow[] drBndCol = dsControls.Tables["Page Controls"].Select("control_attrib_id='" + columnname + "'");
                                                string datatablename = "bnd_" + drBndCol[0]["control_bind_id"].ToString();
                                                if (dsControls.Tables.Contains(datatablename))
                                                {
                                                    DataRow[] datarow = dsControls.Tables[datatablename].Select("Fld='" + dr[dc].ToString().Replace("'", "''") + "'");
                                                    if (datarow.Length > 0)
                                                    {
                                                        dr[dc] = datarow[0]["Val"].ToString().Trim();
                                                    }
                                                    // Below 'else' Added by 'Imran Gani' on 21/03/2012, to add a new master data for bind updation.
                                                    else
                                                    {
                                                        if (autoadd && dr[dc].ToString() != "" && columnname != "owner_id" && dsControls.Tables.Contains("Page Controls") && dsControls.Tables.Contains("bnd_details") && dsControls.Tables.Contains("bnd_access"))
                                                        {
                                                            try
                                                            {
                                                                DataRow[] drBndTable = null;
                                                                DataRow[] drAcc = null;
                                                                if (drBndCol.Length > 0)
                                                                    drBndTable = dsControls.Tables["bnd_details"].Select("af_row_id='" + drBndCol[0]["control_bind_id"].ToString() + "'");
                                                                if (drBndTable.Length > 0)
                                                                    drAcc = dsControls.Tables["bnd_access"].Select("object_table='" + drBndTable[0]["bind_table"].ToString() + "'");
                                                                if (Convert.ToBoolean(drAcc[0]["create"]))
                                                                {
                                                                    DataRow dr1 = dsControls.Tables[datatablename].NewRow();
                                                                    dr1["Fld"] = dr[dc].ToString();
                                                                    dr1["Val"] = getHashKey();
                                                                    dr[dc] = dr1["Val"].ToString();
                                                                    dsControls.Tables[datatablename].Rows.Add(dr1);
                                                                }
                                                                else
                                                                    dr[dc] = "";
                                                            }
                                                            catch (Exception ex)
                                                            {
                                                                dr[dc] = "";
                                                                LogWriter.WriteLog("Error Assign New Value to bind_fields: " + ex.Message);
                                                            }
                                                        }
                                                        else
                                                            dr[dc] = "";
                                                    }
                                                }
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            LogWriter.WriteLog("Error Assign Value to bind_fields: Column : " + ex.Message);
                                        }
                                    }
                                }
                                catch (Exception ex) { LogWriter.WriteLog("Error Assign Value to bind_fields: Row : " + ex.Message); }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error UpdateBindValues :" + ex.Message);
        }

    }


    // This will fetch all data related to the current pageset and return the dataset.
    public DataSet GetTablesRelatedToPageSetID(string pageset_id, DataSet dsobjects, bool ContainsOwnerIDColumn)
    {

        StringBuilder mybuilder = new StringBuilder();
        BindControl BC = new BindControl();
        HDDatabase HDD = new HDDatabase();
        string strbindids = "";
        try
        {
            try
            {
                if (dsobjects.Tables["Page Controls"] != null)
                {
                    if (dsobjects.Tables["Page Controls"].Rows.Count > 0)
                    {
                        DataRow[] datarowbindids = dsobjects.Tables["Page Controls"].Select("control_bind_id <> ''");
                        if (datarowbindids.Length > 0)
                        {
                            string strBindRowID = string.Empty; // Added by 'Imran Gani' on 20/03/2012, To get bind Ids.
                            for (int i = 0; i < datarowbindids.Length; i++)
                            {
                                try
                                {
                                    //if (!ContainsOwnerIDColumn)
                                    //{
                                    string query = BC.GetBindSql(datarowbindids[i]["control_bind_id"].ToString());
                                    //}
                                    //string query = BC.GetBindSql(datarowbindids[i]["control_bind_id"].ToString(),false);

                                    if (query != "")
                                    {
                                        dsobjects.Tables.Add(HDD.GetDataTable(query, "bnd_" + datarowbindids[i]["control_bind_id"].ToString()).Copy());
                                    }
                                    // Below 2 lines Added by 'Imran Gani' on 16/03/2012, To get page control info of required bind table.
                                    GetBindTablePageCtrl(datarowbindids[i]["control_bind_id"].ToString(), dsobjects);
                                    strBindRowID += "'" + datarowbindids[i]["control_bind_id"].ToString() + "',";
                                }
                                catch (Exception ex)
                                {
                                    LogWriter.WriteLog("Error GetTablesRelatedToPageSetID :" + ex.Message);
                                }
                            }
                            GetBindTableAndAcces(strBindRowID, dsobjects); // Added by 'Imran Gani' on 21/03/2012, To get DataTable of bind details and access rights of master table.
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogWriter.WriteLog("import: GetTablesRelatedToPageSetID: Error: " + ex.Message);
            }

            //// Get Master Detail objects
            //// This will get the child pages,menus for those objects where current object is Master
            //try
            //{
            //    //string strsq = "select B.object_id, B.page_id, B.page_name,D.menu_id, C.pageset_id from sy_obj_mas_detail A, sy_pgs_mem B, sy_pgs C, sy_mnu D where A.master_obj = B.object_id and C.pageset_id = D.pageset_id and C.af_row_id = B.pageset_id and master_obj = '" + dsobjects.Tables["Page"].Rows[0]["object_id"].ToString() + "'";

            //    string strsq = "select A.pageset_id as pagesetaf_row_id,C.pageset_id as Pageset_id, A.page_id,A.page_name,A.object_id,D.menu_id  from sy_pgs_mem A, sy_obj B, sy_pgs C, sy_mnu D where A.object_id = B.object_id and A.pageset_id = C.af_row_id and C.pageset_id=D.pageset_id and B.af_row_id in (select object_id from sy_obj_mas_detail where master_obj = '" + dsobjects.Tables["page"].Rows[0]["object_id"].ToString() + "')";
            //    dsobjects.Tables.Add(HDD.GetDataTable(strsq, "DetailofMaster").Copy());
            //}
            //catch (Exception ex)
            //{
            //    LogWriter.WriteLog("Error Getting Detail tables for master: " + ex.Message);
            //}



            //// This will get the master pages,menus for those objects where current object is child
            //try
            //{
            //    string strsql1 = "select A.pageset_id as pagesetaf_row_id,C.pageset_id as Pageset_id, A.page_id,A.page_name,A.object_id,D.menu_id  from sy_pgs_mem A, sy_obj B, sy_pgs C, sy_mnu D where A.object_id = B.object_id and A.pageset_id = C.af_row_id and C.pageset_id=D.pageset_id and B.object_id in (select master_obj from sy_obj_mas_detail where object_id = (select af_row_id from sy_obj where object_id = '" + dsobjects.Tables["Page"].Rows[0]["object_id"].ToString() + "'))";
            //    dsobjects.Tables.Add(HDD.GetDataTable(strsql1, "MasterofDetail").Copy());
            //}
            //catch (Exception ex)
            //{
            //    LogWriter.WriteLog("Error Getting Master tables for Detail: " + ex.Message);
            //}
            return dsobjects;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Getrelatedtables " + ex.Message);
            return dsobjects;
        }
        finally
        {
            HDD = null;
            mybuilder = null;
        }

    }

    ///// <summary>
    ///// To get the DataTable of Bind Details and Access rights of master table.
    ///// </summary>
    ///// <param name="sBnd_ids">Bind Id's of required tables.</param>
    ///// <param name="dsobjects">Dictionary DataSet</param>
    //private void GetBindTableAndAcces(string sBnd_ids, DataSet dsobjects) // Added by 'Imran Gani' on 21/03/2012.
    //{
    //    HDDatabase HDD = new HDDatabase();
    //    try
    //    {
    //        if (sBnd_ids.EndsWith(","))
    //            sBnd_ids = sBnd_ids.Trim(',');
    //        dsobjects.Tables.Add(HDD.GetDataTable("select * from sy_bnd where af_row_id in (" + sBnd_ids + ");", "bnd_details").Copy());
    //        string sql = "select obj.object_table, acc.[read], acc.[create], acc.[edit], acc.[delete] from sy_obj obj inner join sy_obj_acc acc on acc.object_id=obj.object_id inner join sy_bnd B on obj.object_table = B.bind_table and B.af_row_id in (" + sBnd_ids + ") inner join sy_rol rol on rol.af_row_id=acc.role_id inner join sy_usr_rol usr_rol on usr_rol.role_id=rol.af_row_id and user_id in (select af_row_id from sy_usr where user_id='" + HttpContext.Current.Session["userid"].ToString().Replace("'", "''") + "');";
    //        dsobjects.Tables.Add(HDD.GetDataTable(sql, "bnd_access").Copy());
    //    }
    //    catch (Exception ex)
    //    {
    //        LogWriter.WriteLog("Error GetBindTableAndAcces :" + ex.Message);
    //    }
    //}
    
    /// <summary>
    /// Gets the page control details of Master Table(bind table).
    /// </summary>
    /// <param name="sBndId">Bind id of the required table.</param>
    /// <param name="dsobjects">Dictionary DataSet</param>
    private void GetBindTablePageCtrl(string strBindRowID, DataSet dsobjects) // Added by 'Imran Gani' on 16/03/2012.
    {
        HDDatabase HDD = new HDDatabase();
        try
        {
            if (!dsobjects.Tables.Contains("bnd_pg_ctr_" + strBindRowID))
            {
                string strSql = "select * from sy_pg_ctr where page_id = (select af_row_id from sy_pg where page_table_name = (select bind_table from sy_bnd where af_row_id = '" + strBindRowID + "'));";
                dsobjects.Tables.Add(HDD.GetDataTable(strSql, "bnd_pg_ctr_" + strBindRowID).Copy());
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error GetBindTablePageCtrl :" + ex.Message);
        }
    }

    private DataSet UpdateDataType(DataSet ds, string tablename)
    {
        try
        {
            if (ds != null)
                if (ds.Tables.Count > 0)
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        DataSet dstemp = ConvertDataSet(GetTempDataSet(tablename));
                        ds = ConvertDataSet(ds);
                        if (dstemp != null)
                        {
                            ds = CompareDataTypeandUpdate(ds, dstemp);
                        }
                    }

        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("import: UpdateDataType: Error: " + ex.Message);
        }
        return ds;
    }

    private DataSet GetTempDataSet(string tablename)
    {
        DataSet dstemp = new DataSet();
        HDDatabase HDD = new HDDatabase();
        try
        {
            if (tablename.Trim() != "")
            {
                dstemp = HDD.GetDataset("select * from " + tablename + " where 1<>1");
            }

        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetTempDataSet :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return dstemp;
    }
    private DataSet CompareDataTypeandUpdate(DataSet dsSource, DataSet dsTarget)
    {
        DataSet dsResult = new DataSet();
        try
        {
            if (dsSource != null)
            {
                if (dsTarget != null)
                {
                    DataTable dtSource = dsSource.Tables[0];
                    DataTable dtTarget = dsTarget.Tables[0];
                    ArrayList arrUnMatchedColumns = new ArrayList();
                    foreach (DataColumn dcsource in dtSource.Columns)
                    {
                        if (dtTarget.Columns.Contains(dcsource.ColumnName))
                        {
                            DataColumn dctarget = dtTarget.Columns[dcsource.ColumnName];

                            if (dcsource.DataType.Name != dctarget.DataType.Name)
                            {
                                if (dctarget.DataType.Name.ToLower() == "string")
                                    arrUnMatchedColumns.Add(dctarget.ColumnName.ToLower());
                            }
                        }
                    }
                    if (arrUnMatchedColumns.Count > 0)
                    {
                        foreach (DataRow dr in dtSource.Rows)
                        {
                            DataRow drr = dtTarget.NewRow();
                            foreach (DataColumn dc in dtSource.Columns)
                            {
                                if (dtTarget.Columns.Contains(dc.ColumnName.ToLower()))
                                    if (arrUnMatchedColumns.Contains(dc.ColumnName.ToLower()))
                                    {
                                        drr[dc.ColumnName.ToLower()] = dr[dc.ColumnName.ToLower()].ToString();
                                    }
                                    else
                                    {
                                        drr[dc.ColumnName.ToLower()] = dr[dc.ColumnName.ToLower()];
                                    }

                            }

                            dtTarget.Rows.Add(drr);
                            dtTarget.AcceptChanges();
                            //dtTarget.ImportRow(drr);
                        }
                        dsResult.Tables.Add(dtTarget.Copy());
                        dsResult.AcceptChanges();
                    }
                    else
                    {
                        dsResult = dsSource;
                    }

                }
            }

            return dsResult;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : CompareDataTypeandUpdate : " + ex.Message);
            return dsSource;
        }
    }

    // Will change the column names in dataset from upper case to lower case
    private DataSet ConvertDataSet(DataSet ds)
    {
        if (ds != null)
        {
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    try
                    {
                        foreach (DataColumn dc in ds.Tables[0].Columns)
                        {
                            dc.ColumnName = dc.ColumnName.ToLower();
                        }
                        ds.Tables[0].AcceptChanges();
                        ds.AcceptChanges();
                    }
                    catch (Exception ex)
                    {
                        LogWriter.WriteLog("Error : ConvertDataSet :" + ex.Message);
                    }
                    finally
                    {

                    }

                }
            }
        }
        return ds;

    }

    private void AddPageToolTip(DataSet dsPageInfo)
    {
        if (dsPageInfo != null)
            if (dsPageInfo.Tables.Count > 0)
                if (dsPageInfo.Tables["Page"] != null)
                {
                    DataTable dtpage = dsPageInfo.Tables["Page"];
                    if (dtpage.Rows.Count > 0)
                    {
                        lblHeader.Text = lblHeader.Text + getTootipIcon(dtpage.Rows[0]);
                        Literal lit = new Literal();
                        lit.Text = getTooltipContent(dtpage.Rows[0]);

                        td_Page_ToolTip.Controls.Add(lit);
                    }
                }

    }
    private string getTootipIcon(DataRow droPage)
    {
        try
        {
            if (!droPage.Table.Columns.Contains("page_desc")) return "";
            if (droPage["page_desc"].ToString().Trim() == "") return "";
            return "&nbsp;<img id=\"page_tooltip_img\" style=\"cursor:pointer;\" src=\"Images/collapse3.gif\" onClick=\"javascript:showHideTooltipDIV();\" border=\"0\"></img>";
        }
        catch (Exception e)
        {
            return "";
        }
    }

    private string getTooltipContent(DataRow droPage)
    {
        if (!droPage.Table.Columns.Contains("page_desc")) return "";
        if (droPage["page_desc"].ToString().Trim() == "") return "";
        //string ret = "";
        //ret = "<div id=\"page_tooltip_div\" style=\"border-bottom:solid 1px #000000;\">";
        //ret += "<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\" width=\"100%\"><tr><td width=\"10%\" valign=\"center\" align=\"center\"><img src=\"Images/info.gif\" border=\"0\"></img></td><td width=\"80%\" class=\"controlFont\">";
        //ret += droPage["page_desc"].ToString();
        //ret += "</td></tr></table>";
        //ret +="</div>";
        return getContent(HttpContext.Current.Server.MapPath("") + @"\scripts\pagetooltipcontent.txt").Replace("<<content>>", CommonFunctions.SafeEncode(droPage["page_desc"].ToString()));
    }
    private string getContent(string fileName)//created by sanal on 12 Dec 2007
    {
        try
        {
            if (!System.IO.File.Exists(fileName)) return "";
            string temp = "";
            System.IO.StreamReader sr = new System.IO.StreamReader(fileName);
            temp = sr.ReadToEnd();
            sr.Close(); sr = null;
            return temp;
        }
        catch (Exception e)
        {
            return "";
        }
    }

    /// <summary>
    /// This will create a entry in sy_simple_import_statistics before the import process starts
    /// </summary>
    /// <param name="importid">af_row_id of the sy_simple_import record</param>
    /// <param name="dsSource">Source Dataset</param>
    /// <param name="object_id">af_row_id of the Object </param>
    private void LogImportStart(string importid, DataSet dsSource, string object_id)
    {
        ScreenReader SR = new ScreenReader();
        HDDatabase HDD = new HDDatabase();
        try
        {
            int iSourceCount = 0;
            if (dsSource != null)
                if (dsSource.Tables.Count > 0)
                    iSourceCount = dsSource.Tables[0].Rows.Count;
            string sql = "insert into [sy_simple_import_statistics] ([object_id],[inserted_records],[updated_records],[deleted_records],[total_count],[errors],[af_row_id],[created_by],[created_at],[modified_at],[modified_by],[owner_id],[import],[source_count],[start_time],[end_time],[status]) values ('" + object_id + "',null,null,null,0,null,'" + SR.getHashKey() + "','" + Session["user_afrowid"].ToString() + "','" + DateTime.UtcNow + "','" + DateTime.UtcNow + "','" + Session["user_afrowid"].ToString() + "','" + Session["user_afrowid"].ToString() + "','" + importid + "'," + iSourceCount + ",'" + DateTime.UtcNow + "',null,'Import Started')";
            HDD.ExecuteCmdObj(new SqlCommand(sql));
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : LogImportStart : " + ex.Message);
        }
        finally
        {
            SR = null;
            HDD = null;
        }
    }

    private void UpdateImportStatistics(string importid, int insertedcount, string tablename, string strInsertKeys, string error, string status)
    {
        HDDatabase HDD = new HDDatabase();
        try
        {
            string strinsertedRecords = tablename + "(" + strInsertKeys + ")" + sbMasterInserts.ToString();
            string strdeletedRecords = "";// tablename + "()"; // Modified by 'Imran Gani' on 30/03/12.
            string sql = "update sy_simple_import_statistics set inserted_records = '" + strinsertedRecords + "',deleted_records='" + strdeletedRecords + "',total_count=" + insertedcount + ",Errors='" + error + "',end_time='" + DateTime.UtcNow + "',Status='" + status + "', duplicates_count = '" + (int)htRecordsInserted["duplicates"] + "' where import = '" + importid + "'";
            HDD.ExecuteCmdObj(new SqlCommand(sql));
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : UpdateImportStatistics : " + ex.Message);
        }
        finally
        {
            HDD = null;
        }
    }
    private void LoadStats(string object_id)
    {

        HDDatabase HDD = new HDDatabase();
        SecurityFilter SF = new SecurityFilter();
        try
        {
            string sql = "select af_row_id,inserted_records,deleted_records,delete_status,'" + p_simple_import_target_object.SelectedItem.Text + "' as [Object], created_at as [Execution Time],source_count as [Total Records],total_count as [Inserted Records], duplicates_count as [Duplicate Records] from sy_simple_import_statistics where object_id = '" + object_id + "' order by created_at desc";
            //end_time as [Import End Time],
            hfdeleteclick.Value = "0";
            sql = SF.applysecurity(sql);
            DataSet ds = HDD.GetDataset(sql);
            if (ds != null)
                if (ds.Tables.Count > 0)
                {
                    CreateStatsTable(ds.Tables[0], ref td_Import_stats);
                }

        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : LoadStats :" + ex.Message);
        }
        finally
        {
            HDD = null;
            SF = null;
        }
    }
    private void UnusedFields(ref ArrayList list)
    {
        list.Add("af_row_id");
        list.Add("inserted_records");
        list.Add("delete_status");
        list.Add("deleted_records");
    }

    private void CreateStatsTable(DataTable dtInput, ref HtmlTableCell htcresult)
    {
        try
        {
            //string[] OtherFields = { "af_row_id", "inserted_records", "delete_status", "deleted_records" };
            ArrayList list = new ArrayList();
            UnusedFields(ref list);

            if (dtInput != null)
            {
                HtmlTable ht = new HtmlTable();
                HtmlTableRow htr = new HtmlTableRow();
                HtmlTableCell htc = new HtmlTableCell();
                ht.Width = "100%";
                ht.CellPadding = 3;
                ht.CellSpacing = 0;
                ht.Border = 0;


                htr.Attributes.Add("class", "Grid_Header");
                #region Statistics Header
                foreach (DataColumn dc in dtInput.Columns)
                {
                    //if (dc.ColumnName.ToLower() != "af_row_id" && dc.ColumnName.ToLower() != "inserted_records" && dc.ColumnName.ToLower() != "delete_status" && dc.ColumnName.ToLower() != "deleted_records")
                    if (!list.Contains(dc.ColumnName))
                    {
                        htc = new HtmlTableCell();
                        htc.Attributes.Add("class", "Grid_Header");
                        Label lbl = new Label();
                        lbl.Text = dc.ColumnName;
                        htc.Controls.Add(lbl);
                        htr.Controls.Add(htc);
                    }
                }

                htc = new HtmlTableCell();
                Label lblheadertext = new Label();
                lblheadertext.Text = "Delete Inserted Records";
                htc.Controls.Add(lblheadertext);
                htr.Controls.Add(htc);

                ht.Rows.Add(htr);
                #endregion

                #region StatisticsRows
                int iClassCount = 0;
                for (int i = 0; i < dtInput.Rows.Count; i++)
                {
                    htr = new HtmlTableRow();
                    foreach (DataColumn dc in dtInput.Columns)
                    {
                        //if (dc.ColumnName.ToLower() != "af_row_id" && dc.ColumnName.ToLower() != "inserted_records" && dc.ColumnName.ToLower() != "delete_status")
                        if (!list.Contains(dc.ColumnName))
                        {
                            htc = new HtmlTableCell();
                            Label lbl = new Label();
                            lbl.Text = dtInput.Rows[i][dc].ToString();

                            if (dc.ColumnName == "Deleted Record Count")
                            {
                                string value = dtInput.Rows[i]["Deleted Record Count"].ToString();
                                if (value.Trim() == "")
                                    lbl.Text = "0";
                                else
                                    lbl.Text = dtInput.Rows[i]["Inserted Record Count"].ToString();
                            }
                            if (dc.ColumnName == "Execution Time")
                            {
                                lbl.Text = getDateTime_UserFormat(dtInput.Rows[i][dc].ToString());
                            }



                            htc.Controls.Add(lbl);
                            htr.Controls.Add(htc);
                        }
                    }

                    htc = new HtmlTableCell();
                    HiddenField hf = new HiddenField();
                    hf.ID = "hf_af_row_id_" + dtInput.Rows[i]["af_row_id"].ToString();
                    hf.Value = dtInput.Rows[i]["inserted_records"].ToString();
                    htc.Controls.Add(hf);
                    string strstatus = dtInput.Rows[i]["delete_status"].ToString();
                    bool status = false;
                    if (strstatus.Trim() != "")
                        status = Convert.ToBoolean(strstatus);
                    if (status) //Already deleted
                    { // Added by 'Imran Gani' on 30/03/12, to get deleted records info.
                        Label lbl = new Label();
                        lbl.Text = getDeleteMessage(dtInput.Rows[i]["deleted_records"].ToString());
                        htc.Controls.Add(lbl);
                    }
                    else
                    {
                        //Add delete button
                        ImageButton img = new ImageButton();
                        img.ImageUrl = "Images/delete.GIF";
                        img.ID = "img_del_" + dtInput.Rows[i]["af_row_id"].ToString();
                        img.Click += new ImageClickEventHandler(img_Click);
                        img.OnClientClick = "javascript:return DeleteImportedRecords('" + dtInput.Rows[i]["af_row_id"].ToString() + "');";
                        img.CausesValidation = false;
                        img.ToolTip = "Delete records inserted in this import";

                        //Label img = new Label();
                        //img.Text = "<img id=\"img_del_\""+dtInput.Rows[i]["af_row_id"].ToString()+" src=\"Images/delete.GIF\"></img>";
                        //img.Attributes.Add("onclick", "javascript:DeleteAjaxCall('img_del_" + dtInput.Rows[i]["af_row_id"].ToString() + "','" + dtInput.Rows[i]["af_row_id"].ToString() + "');");
                        ////img.Attributes.Add("onclick", "javascript:testAjax();");



                        img.Visible = true;
                        //if (dtInput.Rows[i]["inserted_records"].ToString().Trim() != "")
                        //{
                        //    string insertedafrowids = dtInput.Rows[i]["inserted_records"].ToString();
                        //    string tablename = insertedafrowids.Substring(0, insertedafrowids.IndexOf("("));
                        //    string af_row_ids = "'" + insertedafrowids.Replace(tablename, "").Replace("(", "").Replace(")", "").Replace(";", "','") + "'";
                        //    if (ShowDeleteButton(tablename, af_row_ids))
                        //    {
                        //        img.Visible = true;
                        //    }
                        //}
                        htc.Controls.Add(img);
                    }
                    htr.Controls.Add(htc);
                    //

                    int iClass = iClassCount % 2;
                    htr.Attributes.Add("Class", "Grid_Row" + iClass.ToString());

                    ht.Controls.Add(htr);

                    iClassCount++;
                }

                if (dtInput.Rows.Count == 0)
                {
                    htc = new HtmlTableCell();
                    htr = new HtmlTableRow();
                    Label lblempty = new Label();
                    lblempty.Text = "No records available";
                    lblempty.CssClass = "controlFont";
                    htc.Controls.Add(lblempty);
                    htr.Controls.Add(htc);
                    ht.Controls.Add(htr);
                }
                #endregion

                Label lblresult = new Label();
                lblresult.Controls.Add(ht);
                lblresult.BorderColor = Color.LightGray;
                //lblresult.BorderWidth = Unit.Pixel(1);
                //lblresult.BorderStyle = BorderStyle.Solid;
                lblresult.Width = Unit.Percentage(100);

                htcresult.Controls.Add(lblresult);
            }
        }
        catch (Exception ex)
        { LogWriter.WriteLog("Error : CreateStatsTable :" + ex.Message); }

    }
    /// <summary>
    /// To get message for deleted records.
    /// </summary>
    /// <param name="deleted_records"></param>
    /// <returns>Deleted records info.</returns>
    private string getDeleteMessage(string deleted_records) // Added by 'Imran Gani' on 27/04/12, to get deleted records info.
    {
        string sResult = string.Empty;
        try
        {
            if (deleted_records.StartsWith("Records Deleted :"))
            {
                sResult = deleted_records;
            }
            else
            {
                string TableName = deleted_records.Substring(0, deleted_records.IndexOf("("));
                string[] strKeys = deleted_records.Replace(TableName + "(", "").Replace(")", "").Split(';');
                int count = strKeys.Length - 1;
                sResult = "Records Deleted : </br>" + getDeletedPageName(TableName, count);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : import: getDeleteMessage :" + ex.Message); 
        }
        return sResult;
    }

    void img_Click(object sender, ImageClickEventArgs e)
    {
        ImageButton btn = (ImageButton)sender;
        HtmlForm frmObj = btn.Page.Form;
        HDDatabase HDD = new HDDatabase();
        DynamicDisplayI DDI = new DynamicDisplayI();
        try
        {
            string af_row_id = btn.ID.Replace("img_del_", "");
            string sKeys = "";
            string tablename = "";
            string deleteaf_row_ids = "";
            int totalcount = 0, count = 0;
            string sql = "";
            StringBuilder sbResult = new StringBuilder(); // Added by 'Imran Gani' on 29/03/12, to get deleted records info.

            btn.ImageUrl = "Images/load.gif";

            Control con = DDI.FindControl(frmObj.Controls, "hf_af_row_id_" + af_row_id);
            if (con != null)
            {
                HiddenField hf = (HiddenField)con;
                sKeys = hf.Value;
            }


            if (sKeys.Trim() != "")
            {
                string[] strKeys = sKeys.Split(','); // Added by 'Imran Gani' on 29/03/12, to delete the records which have inserted.
                if (strKeys.Length > 0)
                {
                    for (int i = 0; i < strKeys.Length; i++)
                    {
                        tablename = strKeys[i].Substring(0, strKeys[i].IndexOf("("));

                        string strky = strKeys[i].Replace(tablename, "").Replace("(", "").Replace(")", "");

                        //strky = strky.Replace(";", "','");

                        //if(strky.EndsWith(",'"))
                        //{
                        //    char[] RemoveChar = { '\'', ',' };
                        //    strky = strky.TrimEnd(RemoveChar);

                        //    deleteaf_row_ids = "'" + strky + "'";
                        //}


                        //if (deleteaf_row_ids.EndsWith(","))
                        //    deleteaf_row_ids = deleteaf_row_ids.TrimEnd(',');
                        string strcount = HDD.GetColumnValue("select total_count from sy_simple_import_statistics where af_row_id = '" + af_row_id + "'");
                        totalcount = strcount.Trim() != "" ? Convert.ToInt32(strcount) : 0;
                        DataTable dtPageInfo = new DataTable();
                        if (tablename.Trim() != "")
                        {
                            string tmp_af_row_ids = strky.Replace(";", "','");
                            tmp_af_row_ids = "'" + tmp_af_row_ids + "'";
                            string Ssql = "select * from " + tablename + " where af_row_id in (" + tmp_af_row_ids + ")";
                            dtPageInfo = HDD.GetDataTable(Ssql);
                            loadAdvancedfunction(strky, dtPageInfo, tablename, false); // Added by 'Imran Gani' on 09-Jul-2014, to trigger before delete event.
                            count = PerformDeleteOperation(strky, count, tablename);
                            if (count == -1)
                                break;
                        }
                        sbResult.Append(getDeletedPageName(tablename, count)); // Added by 'Imran Gani' on 27/04/12, to get deleted records info.
                        if (count > 0)
                        {
                            loadAdvancedfunction(strky, dtPageInfo, tablename, true);//Function added by Bala on 10FEb2012 for calling advanced functions in the delete operation.
                        }
                        count = 0; // Added by 'Imran Gani' on 29/03/12, to reset the deleted count as '0' for the next loop.
                    }
                }
                
                if (sbResult.ToString() != "" && count != -1) // Added by 'Imran Gani' on 29/03/12.
                {
                    sql = "update sy_simple_import_statistics set delete_status = 1,deleted_records = 'Records Deleted :</br>" + sbResult.ToString() + "' where af_row_id = '" + af_row_id + "'";
                    HDD.ExecuteCmdObj(new SqlCommand(sql));
                }

                // Below 2 lines are added by 'Imran Gani' on 29/03/12, to reload import statistics.
                td_Import_stats.Controls.Clear();
                LoadStats(p_simple_import_target_object.SelectedValue);

                //if (count == -1)
                //    count = 0;

                ////lbl_message.Text = "Records deleted";
                //Label lbl = (Label)DDI.FindControl(frmObj.Controls, "lblStatusUpload");
                //lbl.Text = wrapItAsMessage("<img src=\"Images/info.gif\" alt=\"info\"></img>&nbsp;Records Deleted :</br>" + sbResult.ToString(), false);
                //lbl.Visible = true;

                //pnlStatistics.Controls.Clear();
                //pnlStatistics.Style["display"] = "none";
            }

        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : Import delete :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }

    }
    /// <summary>
    /// To get page name of the deleted records.
    /// </summary>
    /// <param name="tablename">Table Name where the records got deleted.</param>
    /// <param name="count">No. of deleted records.</param>
    /// <returns>Page Name with No. of deleted records.</returns>
    private string getDeletedPageName(string tablename, int count) // Added by 'Imran Gani' on 27/04/12, to get deleted records info.
    {
        HDDatabase HDD = new HDDatabase();
        string sReturn = string.Empty;
        try
        {
            string PageName = HDD.GetColumnValue("select page_name from sy_pg where page_table_name = '" + tablename + "';");
            sReturn = PageName.Replace("'", "''") + " : " + count + "</br>";
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("import: getDeletedPageName : Error : " + Ex.Message);
        }
        return sReturn;
    }

    private string getDateTime_UserFormat(string Value)
    {
        string sReturn = Value;
        try
        {
            DateTime dt = DateTime.Parse(Value);

            DynamicDisplayI DDI = new DynamicDisplayI();
            String sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");
            DDI = null;
            Localization ll = new Localization();
            sReturn = ll.getDateTime4TimeZone(dt.ToString(), sUTC_Offset, false, true);
            //sReturn = dt.ToString(ll.getDateTimeFormat());
            ll = null;
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("getDateTime_UserFormat : Error : " + Ex.Message);
        }
        return sReturn;
    }

    protected void lnkImportStats_Click(object sender, EventArgs e)
    {
        td_Import_stats.Controls.Clear();
        LoadStats(p_simple_import_target_object.SelectedValue);

        if (pnlStatistics.Style.Value.Contains("none"))
        {
            pnlStatistics.Style.Add(HtmlTextWriterStyle.Display, "block");
        }
        else
        {
            pnlStatistics.Style.Add(HtmlTextWriterStyle.Display, "none");
        }

    }
    private void HideStatsPanel()
    {
        try
        {
            pnlStatistics.Style["display"] = "none";
        }
        catch
        {
        }
    }

    /// <summary>
    /// This will get the afrowids to be deleted in a batch of 250 records
    /// </summary>
    /// <param name="strafrowids"></param>
    /// <returns></returns>
    private void CreateBatchDeleteAfrowids(string strafrowids, ref ArrayList list)
    {
        try
        {
            if (strafrowids.Trim() != "")
            {
                string[] strsplit = strafrowids.Split(';');
                if (strsplit.Length > 0)
                {
                    int iSplitCount = 250; //Default value for split delete count
                    int iTotal = strsplit.Length; // Total number of af_row_ids in the array
                    int iCount = 0;
                    if (iTotal < iSplitCount)
                    {
                        iSplitCount = iTotal;
                        string afrowids = String.Join("','", strsplit, iCount, iSplitCount);
                        list.Add(afrowids);
                    }
                    else
                    {
                        while (iCount < iTotal)
                        {
                            int iRemaining = iTotal - iCount;
                            if (iRemaining <= iSplitCount)
                            {
                                iSplitCount = iRemaining;
                            }
                            string afrowids = String.Join("','", strsplit, iCount, iSplitCount);
                            list.Add(afrowids);
                            iCount = iCount + iSplitCount;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : CreateBatchDeleteAfrowids:" + ex.Message);
        }
        finally
        {

        }
    }

    private int PerformDeleteOperation(string DelAfrowids, int DelCount, string tablename)
    {
        HDDatabase HDD = new HDDatabase();
        try
        {
            if (tablename.Trim() != "")
            {
                ArrayList list = new ArrayList();
                //string ids = "1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16";
                CreateBatchDeleteAfrowids(DelAfrowids, ref list);
                if (list.Count > 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        if (list[i].ToString().Trim() != "")
                        {
                            try
                            {
                                string strafrowids = "'" + list[i].ToString() + "'";
                                int iaffectedcount = HDD.ExecuteCmdObj(new SqlCommand("delete from " + tablename + " where af_row_id in (" + strafrowids + ")"));
                                DelCount = DelCount + iaffectedcount;
                            }
                            catch(Exception ee)
                            {
                                LogWriter.WriteLog("Error : PerformDeleteOperation : " + ee.Message);
                                DelCount = -1;
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : PerformDelete : " + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return DelCount;
    }
    //Function added by Bala on 10FEb2012 for calling advanced functions in the delete operation.
    private void loadAdvancedfunction(string DelAfrowids, DataTable dtPageInfo, string tablename, bool isDel)
    {
        HDDatabase HDD = new HDDatabase();
        DataTable dtPageEvent = new DataTable();
        try
        {
            string strPageKeys = "";
            
            string sql = "select * from sy_pg_evnt where page_id=(select af_row_id from sy_pg where page_table_name='" + tablename + "');";
             dtPageEvent = HDD.GetDataTable(sql);
            foreach (DataRow drPageEvent in dtPageEvent.Rows)
            {
                if (drPageEvent["page_event"].ToString().ToUpper() == "PAD" && isDel)
                    invokeEvent(dtPageInfo, "DELETE", strPageKeys, drPageEvent, tablename, "AFTER_DELETE");
                if (drPageEvent["page_event"].ToString().ToUpper() == "PBD" && !isDel)
                    invokeEvent(dtPageInfo, "DELETE", strPageKeys, drPageEvent, tablename, "BEFORE_DELETE");
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("loadAdvancedfunction : error : " + ex.Message);
        }
        finally
        {
            HDD = null;
            dtPageInfo = null;
            dtPageEvent = null;
        }

    }

    
    private bool ShowDeleteButton(string tablename, string af_row_ids)
    {
        bool status = true;
        HDDatabase HDD = new HDDatabase();
        try
        {
            string sql = "select top 1 af_row_id from " + tablename + " where af_row_id in (" + af_row_ids + ");";
            status = HDD.isDataRecordExists(sql);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error: ShowDeleteButton :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return status;
    }

    /// <summary>
    /// To get the Bind Related Tables of required field.
    /// </summary>
    /// <param name="dsSource">Source DataSet.</param>
    /// <param name="dsobjects">Dictionary DataSet.</param>
    /// <returns>Bind Related Tables if occurs.</returns>
    private DataSet GetBindRelatedTables(DataSet dsSource, DataSet dsobjects) // Added by 'Imran Gani' on 22/03/2012
    {
        StringBuilder strBindRowID = new StringBuilder();
        try
        {
            if (dsobjects.Tables.Contains("Page Controls"))
            {
                DataRow[] datarowbindids = dsobjects.Tables["Page Controls"].Select("control_bind_id <> ''");
                for (int i = 0; i < datarowbindids.Length; i++)
                {
                    if (!strBindRowID.ToString().Contains("'" + datarowbindids[i]["control_bind_id"].ToString() + "'"))
                        strBindRowID.Append("'" + datarowbindids[i]["control_bind_id"].ToString() + "',");
                }
                if (strBindRowID.ToString() != "")
                {
                    GetBindTableAndAcces(strBindRowID.ToString().TrimEnd(','), dsobjects);
                    GetBindDataTable(dsSource, dsobjects); 
                }
            }
            return dsobjects;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error GetBindRelatedTables " + ex.Message);
            return dsobjects;
        }
        finally
        {
            strBindRowID = null;
        }         
    }

    /// <summary>
    /// To get the DataTable of Bind Details and Access rights of master table.
    /// </summary>
    /// <param name="sBnd_ids">Bind Id's of required tables.</param>
    /// <param name="dsobjects">Dictionary DataSet</param>
    private void GetBindTableAndAcces(string strBindRowID, DataSet dsobjects) // Added by 'Imran Gani' on 22/03/2012.
    {
        string sql = string.Empty;
        try
        {
            sql = "select * from sy_import_templates where object_id= (select object_id from sy_obj where af_row_id='" + p_simple_import_target_object.SelectedValue + "');select * from sy_bnd where af_row_id in (" + strBindRowID + "); select obj.af_row_id as object_id, obj.object_table, acc.[read], acc.[create], acc.[edit], acc.[delete] from sy_obj obj inner join sy_obj_acc acc on acc.object_id=obj.object_id inner join sy_bnd B on obj.object_table = B.bind_table and B.af_row_id in (" + strBindRowID + ") inner join sy_rol rol on rol.af_row_id=acc.role_id inner join sy_usr_rol usr_rol on usr_rol.role_id=rol.af_row_id and user_id in (select af_row_id from sy_usr where user_id='" + HttpContext.Current.Session["userid"].ToString().Replace("'", "''") + "');";
            string[] sTableName = { "import_temp", "bnd_details", "bnd_access" };
            GetTables(sql, sTableName, dsobjects);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error GetBindTableAndAcces :" + ex.Message);
        }
        finally
        {
            sql = string.Empty;
        }
    }

    /// <summary>
    /// To Get the Bind DataTable of Bind Fields.
    /// </summary>
    /// <param name="dsSource">Source DataSet.</param>
    /// <param name="dsobjects">Dictionary DataSet.</param>
    private void GetBindDataTable(DataSet dsSource, DataSet dsobjects) // Added by 'Imran Gani' on 22/03/2012.
    {
        BindControl BC = new BindControl();
        HDDatabase HDD = new HDDatabase();
        StringBuilder sbSql = new StringBuilder();
        ArrayList alTableNames = new ArrayList();
        try
        {
            if (dsobjects.Tables.Contains("bnd_details") && dsobjects.Tables.Contains("Page Controls"))
            {
                DataRow[] datarowbindids = dsobjects.Tables["Page Controls"].Select("control_bind_id <> ''");
                for (int i = 0; i < datarowbindids.Length; i++)
                {
                    if (!alTableNames.Contains("bnd_" + datarowbindids[i]["control_bind_id"].ToString()))
                    {
                        string query = BC.GetBindSql(datarowbindids[i]["control_bind_id"].ToString());
                        if (query != "")
                        {
                            string sWhere = string.Empty;
                            DataRow[] drBindCol = dsobjects.Tables["Page Controls"].Select("control_bind_id in ('" + datarowbindids[i]["control_bind_id"].ToString() + "')");
                            foreach (DataRow dr in drBindCol)
                            {
                                string sColName = dr["control_attrib_id"].ToString().ToLower();
                                sWhere += GetColValues(sColName, dr["control_attrib_type"].ToString(), dsSource);
                            }
                            DataRow[] drBind_id = dsobjects.Tables["bnd_details"].Select("af_row_id ='" + datarowbindids[i]["control_bind_id"].ToString() + "'");
                            if (sWhere != "" && sWhere.EndsWith(","))
                            {
                                sWhere = sWhere.TrimEnd(',');
                                query = query.Replace("where 1=1", "where " + drBind_id[0]["bind_text"].ToString() + " in (" + sWhere + ") ");
                            }
                            sbSql.Append(query);
                            alTableNames.Add("bnd_" + datarowbindids[i]["control_bind_id"].ToString());

                            query = "select * from sy_pg_ctr where page_id = (select af_row_id from sy_pg where page_table_name = '" + drBind_id[0]["bind_table"].ToString() + "');";
                            sbSql.Append(query);
                            alTableNames.Add("bnd_pg_ctr_" + datarowbindids[i]["control_bind_id"].ToString());
                        }
                    }
                }
                if (sbSql.ToString() != "")
                {
                    GetTables(sbSql.ToString(), (string[])alTableNames.ToArray(typeof(string)), dsobjects);
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error GetBindRelatedTables " + ex.Message);
        }
        finally
        {
            HDD = null;
            BC = null;
            alTableNames = null;
            sbSql = null;
        }  
    }

    /// <summary>
    /// To Get the column values of the DataTable.
    /// </summary>
    /// <param name="sColName">Column Name of the DataTable.</param>
    /// <param name="sDataType">DataType of the Column.</param>
    /// <param name="dsSource">Source DataSet.</param>
    /// <returns>Data Value of the column.</returns>
    private string GetColValues(string sColName, string sDataType, DataSet dsSource) // Added by 'Imran Gani' on 22/03/2012.
    {
        StringBuilder sbResult = new StringBuilder();
        string sResult = string.Empty;
        try
        {
            if (dsSource.Tables[0].Columns.Contains(sColName))
            {
                DataRow[] dr = dsSource.Tables[0].Select(sColName + "<>''");
                string temp = (sDataType == "NU" || sDataType == "AN" || sDataType == "FL") == true ? "" : "'";
                for (int i = 0; i < dr.Length; i++)
                {
                    sbResult.Append(temp + dr[i][sColName].ToString().Replace("'", "''") + temp + ",");
                }
            }
            sResult = sbResult.ToString();
            
            return sResult;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error GetColValues : " + ex.Message);
            return sResult;
        }
        finally
        {
            sbResult = null;
            sResult = string.Empty;
        }
    }

    /// <summary>
    /// To Get a collection of DataTable with their table name.
    /// </summary>
    /// <param name="Sql">Sql Query of the DataTable.</param>
    /// <param name="sTableName">Table Name for the DataTable.</param>
    /// <param name="dsobjects">Dictionary DataSet.</param>
    private void GetTables(string Sql, string[] sTableName, DataSet dsobjects) // Added by 'Imran Gani' on 22/03/2012.
    {
        HDDatabase HDD = new HDDatabase();
        DataSet ds = new DataSet();
        try
        {
            ds = HDD.GetDataset(Sql);
            if (ds != null && sTableName != null)
            {
                if (ds.Tables.Count == sTableName.Length)
                {
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {
                        ds.Tables[i].TableName = sTableName[i];
                        dsobjects.Tables.Add(ds.Tables[i].Copy());
                    }
                }
            } 
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error GetTables " + ex.Message);
        }
        finally
        {
            HDD = null;
            sTableName = null;
            Sql = string.Empty;
            if (ds != null)
                ds.Dispose();
        } 
    }

    /// <summary>
    /// To load related fields of an object for avoiding duplicates
    /// </summary>
    /// <param name="sObjectID">selected object id</param>
    private void loadObjectFieldsToAvoidDuplicates(string sObjectID)
    {
        HDDatabase hdd = new HDDatabase();
        DataTable dtObjAtt = new DataTable();
        try
        {
            p_simple_import_avoid_duplicates_based_on.Items.Clear();
            DataRow dr = hdd.GetTableRow("select * from sy_acr_mem where af_row_id = '6df49e103aa6a359';");
            if (Convert.ToBoolean(dr["mem_value3"].ToString()))
                p_simple_import_avoid_duplicates_based_on.Items.Add(new ListItem(dr["mem_value1"].ToString(), dr["mem_value2"].ToString()));
            dtObjAtt = hdd.GetDataTable("select attrib_name, af_row_id from sy_obj_att where object_id = '" + sObjectID + "' and attrib_id not in ('af_row_id', 'created_by', 'owner_id', 'created_at', 'modified_at', 'modified_by') order by attrib_seq;");
            if (dtObjAtt != null && dtObjAtt.Rows.Count > 0)
            {
                foreach (DataRow drFlds in dtObjAtt.Rows)
                {
                    p_simple_import_avoid_duplicates_based_on.Items.Add(new ListItem(drFlds["attrib_name"].ToString(), drFlds["af_row_id"].ToString()));
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Import : loadObjectFieldsToAvoidDuplicates " + ex.Message);
        }
        finally
        {
            hdd = null;
            if (dtObjAtt != null)
                dtObjAtt.Dispose();
        } 
    }

    /// <summary>
    /// To handle duplicate records from import transaction (excel/csv).
    /// </summary>
    /// <param name="dsSource">DataTable of imprt records.</param>
    /// <param name="sTableName">Table name of import transaction.</param>
    // Added by 'Imran Gani' on 30-Jul-2013, to handle duplicate records from import.
    private void HandleDuplicateRecords(DataSet dsSource, string sTableName)
    {
        HDDatabase hdd = new HDDatabase();
        DataTable dtDuplicates = new DataTable();
        DataTable dtTarget = new DataTable();
        DataTable dtUniqueFlds = new DataTable();
        string sCol = string.Empty, sVal = string.Empty;
        try
        {
            htRecordsInserted["totalrecords"] = dsSource.Tables[0].Rows.Count;
            htRecordsInserted["duplicates"] = 0;
            dtDuplicates = dsSource.Tables[0].Clone();
            if (p_simple_import_avoid_duplicates_based_on.SelectedValue == "-default_settings-")
            {
                string is_composite = hdd.GetColumnValue("select is_composite from sy_obj where af_row_id = '" + p_simple_import_target_object.SelectedValue + "';");
                if (Convert.ToBoolean((is_composite.ToLower() == "true" ? 1 : 0)))
                {
                    HandleDuplicatesForCompositeKey(dsSource, sTableName, dtDuplicates);
                    return;
                }
                dtUniqueFlds = hdd.GetDataTable("select attrib_id from sy_obj_att where object_id = '" + p_simple_import_target_object.SelectedValue + "' and is_primary = 1 order by attrib_seq;");
            }
            else
                dtUniqueFlds = hdd.GetDataTable("select attrib_id from sy_obj_att where af_row_id in ('" + p_simple_import_avoid_duplicates_based_on.SelectedValue + "');");
            foreach (DataRow drFlds in dtUniqueFlds.Rows)
            {
                sCol = drFlds["attrib_id"].ToString();
                sVal = string.Empty;
                if (!dsSource.Tables[0].Columns.Contains(sCol)) continue;
                foreach (DataRow dr in dsSource.Tables[0].Rows)
                {
                    if (!sVal.Contains("'" + dr[sCol].ToString() + "'"))
                        sVal += " '" + dr[sCol].ToString() + "',";
                }
                // Check duplicates from target table.
                dtTarget = hdd.GetDataTable("select distinct " + sCol + " from " + sTableName + " where " + sCol + " in (" + sVal.TrimEnd(',') + ");");
                if (dtTarget != null && dtTarget.Rows.Count > 0)
                {
                    sVal = string.Empty;
                    foreach (DataRow dr in dtTarget.Rows)
                    {
                        sVal += " '" + dr[sCol].ToString() + "',";
                    }
                    // remove duplicates from source table.
                    DataRow[] drSrc = dsSource.Tables[0].Select(sCol + " in (" + sVal.TrimEnd(',') + ")");
                    foreach (DataRow dr in drSrc)
                    {
                        dtDuplicates.ImportRow(dr);
                        dsSource.Tables[0].Rows.Remove(dr);
                    }
                }
                // Check duplicates from source table - import
                sVal = string.Empty;
                foreach (DataRow dr in dsSource.Tables[0].Select())
                {
                    if (!sVal.Contains("'" + dr[sCol].ToString() + "'"))
                        sVal += " '" + dr[sCol].ToString() + "',";
                    else
                    {
                        dtDuplicates.ImportRow(dr);
                        dsSource.Tables[0].Rows.Remove(dr);
                    }
                }
            }
            htRecordsInserted["duplicates"] = dtDuplicates.Rows.Count;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Import : HandleDuplicateRecords " + ex.Message);
        }
        finally
        {
            hdd = null;
            if (dtUniqueFlds != null)
                dtUniqueFlds.Dispose();
            if (dtDuplicates != null)
                dtDuplicates.Dispose();
            if (dtTarget != null)
                dtTarget.Dispose();
        }
    }

    /// <summary>
    /// To handle duplicate records for composite key.
    /// </summary>
    /// <param name="dsSource">Source dataset to be import.</param>
    /// <param name="sTableName">Target object - table name.</param>
    /// <param name="dtDuplicates">DataTable to carry duplicates.</param>
    // Added by 'Imran Gani' on 11-Sep-2013, to handle duplicate records for composite key from import.
    private void HandleDuplicatesForCompositeKey(DataSet dsSource, string sTableName, DataTable dtDuplicates)
    {
        HDDatabase hdd = new HDDatabase();
        DataTable dtTempSource = new DataTable();
        DataTable dtTarget = new DataTable();
        DataTable dtUniqueFlds = new DataTable();
        string sCol = string.Empty, sVal = string.Empty, sWhereCond = string.Empty;
        try
        {
            dtTempSource = dsSource.Tables[0].Copy();
            dtUniqueFlds = hdd.GetDataTable("select attrib_id from sy_obj_att where object_id = '" + p_simple_import_target_object.SelectedValue + "' and is_primary = 1 order by attrib_seq;");
            foreach (DataRow drSrcRow in dtTempSource.Rows)
            {
                sWhereCond = string.Empty;
                foreach (DataRow drFlds in dtUniqueFlds.Rows)
                {
                    sCol = drFlds["attrib_id"].ToString();
                    if (!dsSource.Tables[0].Columns.Contains(sCol)) continue;
                    sWhereCond += sCol + " = '" + drSrcRow[sCol].ToString() + "' and ";
                }
                sWhereCond = sWhereCond.Substring(0, sWhereCond.LastIndexOf(" and "));
                if (sVal.Contains(sWhereCond))
                    continue;
                else
                    sVal += "[ " + sWhereCond + " ], ";
                // Check duplicates from target table.
                dtTarget = hdd.GetDataTable("select * from " + sTableName + " where " + sWhereCond + ";");
                if (dtTarget != null && dtTarget.Rows.Count > 0)
                {
                    // remove duplicates from source table.
                    DataRow[] drSrc = dsSource.Tables[0].Select(sWhereCond);
                    foreach (DataRow dr in drSrc)
                    {
                        dtDuplicates.ImportRow(dr);
                        dsSource.Tables[0].Rows.Remove(dr);
                    }
                }
                // Check duplicates from source table - import
                bool IsFirstRow = true;
                foreach (DataRow dr in dsSource.Tables[0].Select(sWhereCond))
                {
                    if (!IsFirstRow)
                    {
                        dtDuplicates.ImportRow(dr);
                        dsSource.Tables[0].Rows.Remove(dr);
                    }
                    IsFirstRow = false;
                }
            }
            htRecordsInserted["duplicates"] = dtDuplicates.Rows.Count;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Import : HandleDuplicateRecords " + ex.Message);
        }
        finally
        {
            hdd = null;
            if (dtUniqueFlds != null)
                dtUniqueFlds.Dispose();
            if (dtTempSource != null)
                dtTempSource.Dispose();
            if (dtTarget != null)
                dtTarget.Dispose();
        }
    }

    private void getselectedFieldToAvoidDuplicates()
    {
        try
        {
            if (hf_avoid_duplicates_based_on.Value == "" || hf_avoid_duplicates_based_on.Value == "undefined")
                p_simple_import_avoid_duplicates_based_on.SelectedValue = "-default_settings-";
            else
                p_simple_import_avoid_duplicates_based_on.SelectedValue = hf_avoid_duplicates_based_on.Value;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Import : getselectedFieldToAvoidDuplicates " + ex.Message);
        }
    }

    /// <summary>
    /// To allow options in advance options
    /// </summary>
    private void checkAdvanceOptions()
    {
        HDDatabase hdd = new HDDatabase();
        string sOption = string.Empty;
        try
        {
            // For Avoid Duplicate option
            sOption = hdd.GetColumnValue("select mem_value1 from sy_acr_mem where af_row_id = '8edbf59b1246c44e' and acr_id = '6fcb183a4781114e';");
            if (!Convert.ToBoolean(sOption.ToLower()))
                tbl_advanced_options.Visible = false;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Import : getselectedFieldToAvoidDuplicates " + ex.Message);
        }
        finally
        {
            hdd = null;
        }
    }
    
    /// <summary>
    /// To check whether import transaction (excel/csv) has empty/null value for required fields.
    /// </summary>
    /// <param name="dsSource">DataTable of imprt records.</param>
    /// <param name="sExpectedFld">Referance string to get field name of required field.</param>
    /// <returns>True/False, if empty/null found.</returns>
    // Added by 'Imran Gani' on 11-Sep-2013, to check null for unique field during import.
    private bool checkNullForUniqueFlds(DataTable dtSource, ref string sExpectedFld)
    {
        bool ret = true;
        HDDatabase hdd = new HDDatabase();
        DataTable dtUniqueFlds = new DataTable();
        try
        {
            dtUniqueFlds = hdd.GetDataTable("select attrib_id from sy_obj_att where object_id = '" + p_simple_import_target_object.SelectedValue + "' and is_primary = 1 order by attrib_seq;");
            foreach (DataRow drFlds in dtUniqueFlds.Rows)
            {
                sExpectedFld = drFlds["attrib_id"].ToString();
                if (!dtSource.Columns.Contains(sExpectedFld)) continue;
                DataRow[] dr = dtSource.Select(sExpectedFld + " = '' or " + sExpectedFld + " is null");
                if (dr.Length > 0)
                {
                    ret = false;
                    break;
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error Import : checkNullForUniqueFlds " + ex.Message);
        }
        finally
        {
            hdd = null;
            dtUniqueFlds.Dispose();
        }
        return ret;
    }

    /// <summary>
    /// To update the encrypted values.
    /// </summary>
    /// <param name="dt">Input datatable</param>
    /// <param name="dtControls">Page control table.</param>
    // Added by 'Imran Gani' on 10-Jul-2014, to handle encryption fields.
    private void UpdateEncryptionField(DataTable dt, DataTable dtControls)
    {
        try
        {
            foreach (DataRow aDr in dtControls.Select("control_html in ('PW', 'ENC')"))
            {
                string colName = (string)aDr["control_attrib_id"];
                if (dt.Columns.Contains(colName.ToLower()))
                {
                    foreach (DataRow bDr in dt.Select(colName + " IS NOT NULL"))
                    {
                        bDr[colName] = security.encrypt(bDr[colName].ToString());
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error UpdateEncryptionField :" + ex.Message);
        }

    }
}